SQL SERVER – Weekly Series – Memory Lane – #038
- by Pinal Dave
Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.
2007
CASE Statement in ORDER BY Clause – ORDER BY using Variable
This article is as per request from the Application Development Team Leader of my company. His team encountered code where the application was preparing string for ORDER BY clause of the SELECT statement. Application was passing this string as variable to Stored Procedure (SP) and SP was using EXEC to execute the SQL string. This is not good for performance as Stored Procedure has to recompile every time due to EXEC. sp_executesql can do the same task but still not the best performance.
SSMS – View/Send Query Results to Text/Grid/Files
Results to Text – CTRL + T
Results to Grid – CTRL + D
Results to File – CTRL + SHIFT + F
2008
Introduction to SPARSE Columns Part 2
I wrote about Introduction to SPARSE Columns Part 1. Let us understand the concept of the SPARSE column in more detail. I suggest you read the first part before continuing reading this article. All SPARSE columns are stored as one XML column in the database. Let us see some of the advantage and disadvantage of SPARSE column.
Deferred Name Resolution
How come when table name is incorrect SP can be created successfully but when an incorrect column is used SP cannot be created?
2009
Backup Timeline and Understanding of Database Restore Process in Full Recovery Model
In general, databases backup in full recovery mode is taken in three different kinds of database files.
Full Database Backup
Differential Database Backup
Log Backup
Restore Sequence and Understanding NORECOVERY and RECOVERY
While doing RESTORE Operation if you restoring database files, always use NORECOVER option as that will keep the database in a state where more backup file are restored. This will also keep database offline also to prevent any changes, which can create itegrity issues. Once all backup file is restored run RESTORE command with a RECOVERY option to get database online and operational.
Four Different Ways to Find Recovery Model for Database
Perhaps, the best thing about technical domain is that most of the things can be executed in more than one ways. It is always useful to know about the various methods of performing a single task.
Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database
When Information Schema is used, we will not be able to discern between primary key and foreign key; we will have both the keys together. In the case of sys schema, we can query the data in our preferred way and can join this table to another table, which can retrieve additional data from the same.
Get Last Running Query Based on SPID
PID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.
2010
SELECT * FROM dual – Dual Equivalent
Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X. You can check the content of the DUAL table using the following syntax. SELECT * FROM dual
Identifying Statistics Used by Query
Someone asked this question in my training class of query optimization and performance tuning. “Can I know which statistics were used by my query?”
2011
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31
What are the basic functions for master, msdb, model, tempdb and resource databases?
What is the Maximum Number of Index per Table?
Explain Few of the New Features of SQL Server 2008 Management Studio
Explain IntelliSense for Query Editing
Explain MultiServer Query
Explain Query Editor Regions
Explain Object Explorer Enhancements
Explain Activity Monitors
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31
What is Service Broker?
Where are SQL server Usernames and Passwords Stored in the SQL server?
What is Policy Management?
What is Database Mirroring?
What are Sparse Columns?
What does TOP Operator Do?
What is CTE?
What is MERGE Statement?
What is Filtered Index?
Which are the New Data Types Introduced in SQL SERVER 2008?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31
What are the Advantages of Using CTE?
How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
What is CLR?
What are Synonyms?
What is LINQ?
What are Isolation Levels?
What is Use of EXCEPT Clause?
What is XPath?
What is NOLOCK?
What is the Difference between Update Lock and Exclusive Lock?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 17 of 31
How will you Handle Error in SQL SERVER 2008?
What is RAISEERROR? What is RAISEERROR?
How to Rebuild the Master Database?
What is the XML Datatype?
What is Data Compression?
What is Use of DBCC Commands?
How to Copy the Tables, Schema and Views from one SQL Server to Another?
How to Find Tables without Indexes?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31
How to Copy Data from One Table to Another Table?
What is Catalog Views?
What is PIVOT and UNPIVOT?
What is a Filestream?
What is SQLCMD?
What do you mean by TABLESAMPLE?
What is ROW_NUMBER()?
What are Ranking Functions?
What is Change Data Capture (CDC) in SQL Server 2008?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31
How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
What is the CPU Pressure?
How can I Get Data from a Database on Another Server?
What is the Bookmark Lookup and RID Lookup?
What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
How can I Check that whether Automatic Statistic Update is Enabled or not?
How to Find Index Size for Each Index on Table?
What is the Difference between Seek Predicate and Predicate?
What are Basics of Policy Management?
What are the Advantages of Policy Management?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 20 of 31
What are Policy Management Terms?
What is the ‘FILLFACTOR’?
Where in MS SQL Server is ’100’ equal to ‘0’?
What are Points to Remember while Using the FILLFACTOR Argument?
What is a ROLLUP Clause?
What are Various Limitations of the Views?
What is a Covered index?
When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
What are Wait Types?
How to Stop Log File Growing too Big?
If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?
2012
Example of Width Sensitive and Width Insensitive Collation
Width Sensitive Collation: A single-byte character (half-width) represented as single-byte and the same character represented as a double-byte character (full-width) are when compared are not equal the collation is width sensitive. In this example we have one table with two columns. One column has a collation of width sensitive and the second column has a collation of width insensitive.
Find Column Used in Stored Procedure – Search Stored Procedure for Column Name
Very interesting conversation about how to find column used in a stored procedure. There are two different characters in the story and both are having a conversation about how to find column in the stored procedure. Here are two part story Part 1 | Part 2
SQL SERVER – 2012 Functions – FORMAT() and CONCAT() – An Interesting Usage
Generate Script for Schema and Data – SQL in Sixty Seconds #021 – Video
In simple words, in many cases the database move from one place to another place. It is not always possible to back up and restore databases. There are possibilities when only part of the database (with schema and data) has to be moved. In this video we learn that we can easily generate script for schema for data and move from one server to another one.
INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1
I often see the value -1 in the CHARACTER_MAXIMUM_LENGTH column of INFORMATION_SCHEMA.COLUMNS table. I understand that the length of any column can be between 0 to large number but I do not get it when I see value in negative (i.e. -1). Any insight on this subject?
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
