Some Notes about SQL Index Fragmentation

This post is about a recent reading/research that I have made regarding of how to identify ‘bad’ indexes that have been defragmented much and how to fix those to be optimal again. There are many references put on this post such as useful scripts and articles for further reading.

Identifying Defragmented Indexes
Firstly, we would need to find the indexes that have much fragmentation. Below is a useful script from Microsoft Script Center site. This script shows average fragmentation for each index in all tables and indexed views.

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 0--You can specify the percent as you want
ORDER BY indexstats.avg_fragmentation_in_percent DESC

What to Do with the Result?
From the result we can either choose to ignore, reorganise or rebuild each index.

Reorganising an index is to reorder and clean up the index with pre-existing settings. While rebuilding an index is to recreate the index from scratch. When rebuilding an index, new settings can be set as the old index will be deleted. Rebuilding an index is usually more effective than reorganising an index. However rebuilding an index will cost more.

Reorganising an index is always done online while rebuilding is offline (except if using SQL Server Enterprise edition). Stopping a rebuilding operation will make the operation to be rolled back while stopping reorganising operation will just stop the process and leave the done parts.

According to Microsoft guideline, if an index has
- less than 5 % fragmentation -> ignore
- between 5% to 30% fragmentation -> reorganise
- greater than 30 % fragmentation -> rebuild


How to Reorganise / Rebuild Index?
An index can be reorganised or rebuilt with Alter Index command. For example:

ALTER INDEX IX_MyTable_IndexName ON MyTable REORGANIZE;   
ALTER INDEX IX_MyTable_IndexName ON MyTable REBUILD;

To see all options for the command, see this MSDN documentation.


Reorganise / Rebuild all Indexes in the Database
To do this, we can use Maintenance Plan Wizard provided by SQL Server or script.

To create a maintenance plan:
1. expand the Management folder in the target database server
2. right click Maintenance Plans folder and select Maintenance Plan Wizard

For more details about using Maintenance Plan Wizard to rebuild indexes, please see this article 'Rebuilding Indexes using the SSMS Database Maintenance Wizard'.

Otherwise we can use script to reorganise/rebuild indexes. An example of simple script to rebuild all indexes of tables and indexed views in a database (source is http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/06/20/rebuild-all-the-indexes-of-a-sql-database-in-one-go/):

DECLARE @tsql NVARCHAR(MAX) 
DECLARE @fillfactor INT

SET @fillfactor = 90

SELECT @tsql =
STUFF(( SELECT DISTINCT
';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
FROM
sysobjects o
INNER JOIN sysindexes i
ON o.id = i.id
WHERE
o.xtype IN ('U','V')
AND i.name IS NOT NULL
FOR XML PATH('')), 1,1,'')

--PRINT @tsql         
EXEC sp_executesql @tsql

Or we can use more sophisticated script that has been used and proven by many people like Index Defrag Script.


I have Done Rebuilt but the Index Fragmentation is Still High
Fragmentation in an index of a small table may not be reduced even after reorganising or rebuilding because they may be stored on mixed extents that are shared with different objects. We might want to check as well whether by having the index is actually helping to improve query performance or not. If not then this index can be considered to be removed.


References and further reading:
Reorganize and Rebuild Indexes
Rebuild or Reorganize: SQL Server Index Maintenance

Projecting Rows into Columns

Say we have some data in different rows of a column that we would like to project in different columns. For example we have this:

and would like to transform into this:


To do the projection we can use Pivot feature:

SELECT StudentId, DisciplineId, [1] AS Course1, [2] AS Course2, [3] AS Course3, [4] AS Course4, [5] AS Course5
FROM 
(
 SELECT  StudentId, DisciplineId, CompletionDate, CourseId
 FROM TrainingDetails
) AS T1
PIVOT 
( 
 MAX (CompletionDate) FOR CourseId IN ([1], [2], [3], [4], [5])
) AS T2

Pivot will project the rows into columns. It will also automatically apply grouping to the rest of the columns. So it is important to only feed the query with same columns that will be used in the Select result. In this example, we narrow down the source to only have columns that will be used in query (StudentId, DisciplineId, CompletionDate and CourseId) from other unrelated columns in the source (TrainingDetails table). If there is any extra column, the grouping will not be done correctly.

However, we can also achieve the same result with a more standard query:

SELECT StudentId, DisciplineId
, MAX(CASE WHEN CourseId = 1 THEN CompletionDate END) AS CompletionDateCourse1
, MAX(CASE WHEN CourseId = 2 THEN CompletionDate END) AS CompletionDateCourse2
, MAX(CASE WHEN CourseId = 3 THEN CompletionDate END) AS CompletionDateCourse3
, MAX(CASE WHEN CourseId = 4 THEN CompletionDate END) AS CompletionDateCourse4
, MAX(CASE WHEN CourseId = 5 THEN CompletionDate END) AS CompletionDateCourse5
FROM TrainingDetails
GROUP BY StudentId, DisciplineId

Bulk Insert in Web SQL

Below is a snippet of how to do bulk insert of records in Web SQL:

// db is the database object that is usually initialise with openDatabase() function
db.transaction(function (tx) {  
  // insert each record
  $.each(myArray, function (i, item) {
   tx.executeSql("INSERT INTO MyTable(name, value) VALUES (?, ?)", [item.name, item.value]);
  });   
},
// error
function (error) {
 . . .
},
// success - the transaction() function does not pass any object to its success callback
function () {
 . . .
});

Web SQL does not understand the Standard SQL bulk insert syntax such as
Insert Into tbl (col1, col2) Values ('val1', 'val2'), ('val3', 'val4'), ...
but each insert statement needs to be executed using executeSql() function. A transaction is usually used to wrap these insert commands.

Concatenating Results in SQL Query

Below is an example to concatenate results in an SQL query:

SELECT S.FirstName, S.LastName,
  STUFF( 
    ( SELECT ',' + S1.FirstName + ' ' + S1.LastName FROM Student S1 WHERE S1.FirstName = S.FirstName FOR XML PATH('') ), 
    1, 
    1, 
    ''
  ) AS AnyStudentsWithSimilarNames
FROM Student S

In the example, we use FOR XML PATH('') to concatenate the result from multiple rows into a single value.

We also use STUFF() function to simply remove the first occurrence of ',' character. The syntax is STUFF( expression, starting_character_position, length, replace_with_expression ).

If we have '<', '>' or '&' characters in our projection and want to avoid those getting encoded, we can replace the codes inside STUFF() function to:

(SELECT ... FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')

Here we add TYPE to have the query with FOR XML PATH() returns XML data type then we use VALUE() function to get the value. Both are used as a work around to avoid the characters getting encoded.

TransactionScope and SaveChanges in Entity Framework

TransactionScope class in .Net is great but if not used properly can cause table locks for long time and suffer application performance.

When using it with Entity Framework, only use TransactionScope when operation cannot be done within one SaveChanges() method or involves more than one data context.

Let's see the following codes. Imagine for some reasons, two data contexts are used.

using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
    // some codes that do not involve database

    // some queries
	var student = contextOne.Students.Where( . . . );
	var schoolList = contextTwo.Schools;
	
	// more queries and validations
	//		check if student is allowed to move out ...
	//		check if student is allowed to move in ...
	
	// update student
	student.School = newSchool;
	
	// update some data in school context
	. . .
	
	
	contextOne.SaveChanges();
	contextTwo.SaveChanges();
	
    scope.Complete();
}

When we check SQL Profiler with tracing transactions enabled, we can see that Begin Transaction is executed immediately before the first database related operation. In this case is before the first data context querying a student (line #6). The transaction is wrapped up after the two data contexts are updated. This is a long time of locking and far beyond the necessary.

To enable tracing transactions, go to 'Events Selection' tab, click 'Show all events' then scroll to almost the end, expand 'Transactions' and tick the ones starting with 'TM: ...'

What should have been done is like the following:

    // some codes that do not involve database

    // some queries
	var student = contextOne.Students.Where( . . . );
	var schoolList = contextTwo.Schools;
	
	// more queries and validations
	//		check if student is allowed to move out ...
	//		check if student is allowed to move in ...
	
	// update student
	student.School = newSchool;
	
	// update some data in school context
	. . .
	

    using (var scope = new TransactionScope(TransactionScopeOption.Required))
    {	
	    contextOne.SaveChanges();
	    contextTwo.SaveChanges();
	
        scope.Complete();
    }

You can add try catch as well around the codes and discard the changes when there is an error.


Secondly, if there is only one data context needs to be updated, TransactionScope is not needed. Calling SaveChanges() method alone is enough and will create a transaction in database and execute any changes that have been made to the objects within the context.


For more information about TransactionScope, please see my previous article.

Capturing Deadlock Info in SQL Server with System Health and Event Notification

System Health
SQL Server 2008 introduced a new way to capture deadlock information, which is by using system_health event session in Extended Events. This is run by default in the server. To see recent deadlocks that have occurred, run this query:

WITH SystemHealth
 AS (
 SELECT CAST(target_data as xml) AS TargetData
 FROM sys.dm_xe_session_targets st
 JOIN sys.dm_xe_sessions s
 ON s.address = st.event_session_address
 WHERE name = 'system_health'
 AND st.target_name = 'ring_buffer')
 
 SELECT XEventData.XEvent.value('(data/value)[1]','VARCHAR(MAX)') AS DeadLockGraph
 FROM SystemHealth
 CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
 WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'

Copy the content of each row and save into an .XDL file then open the file on SQL Management Studio. It happened to me that I received this message "The 'victim-list' start tag on line 1 does not match the end tag of 'deadlock' ..." when SSMS is trying to open the file. When I checked the file, there are two nodes immediately after the <deadlock-list> node that do not match. Not sure why they are generated incorrectly. Then I changed those nodes:

<victim-list>
  <victimProcess id="processNumber"/>

to

<deadlock victim="processNumber"/>

Basically we want the file content to have this structure:

<deadlock-list>
  <deadlock victim="...">
    <process-list>
  . . .
 </process-list>
    <resource-list>
  . . .
 </resource-list>
  </deadlock>
</deadlock-list>

Now I could see a deadlock graph is displayed graphically.



Service Broker Event Notification
In SQL Server 2005 or above, we can also use Event Notification to capture deadlock information. We can send the event to a service and then to a queue. Below are the scripts to setup the event notification, service and queue:

-- Need to use a broker enabled database
USE msdb;

--  Create a service broker queue to hold the events
CREATE QUEUE DeadlockQueue
GO

--  Create a service broker service to receive the events and route to the queue
CREATE SERVICE DeadlockService
ON QUEUE DeadlockQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

-- Create the event notification for capturing deadlock graphs and send to the service
CREATE EVENT NOTIFICATION CaptureDeadlocks
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE 'DeadlockService', 'current database' ;
GO 

Each time a deadlock happen, the DeadlockQueue will be added.

USE msdb ;

-- try to see DeadlockQueue data
select * from DeadlockQueue

Then to see the contending queries of the first deadlock before one of them is chosen as a deadlock victim:

USE msdb;
-- Cast message_body to XML and query deadlock graph from TextData
SELECT  message_body.value('(/EVENT_INSTANCE/TextData/
                                  deadlock-list)[1]', 'varchar(max)')
                                  AS DeadlockGraph
FROM    ( SELECT    CAST(message_body AS XML) AS message_body
          FROM      DeadlockQueue
        ) AS sub ;
GO

To get the xml data of the deadlocks one by one:

DECLARE @message_body XML ;

RECEIVE TOP(1) -- just handle one message at a time
@message_body=message_body
FROM DeadlockQueue ;

SELECT  @message_body.query('(/EVENT_INSTANCE/TextData/deadlock-list)[1]') AS XML
GO

Then we can save it as an .XDL file and open in SSMS.

After we have done, drop the instances

drop event notification CaptureDeadlocks on server

drop service DeadlockService

drop queue DeadlockQueue

Example scripts to create a deadlock
Below is an example of how to create a deadlock. We just need to pick two tables and open two SSMS query windows to try these.

-- open window 1 and run this
begin tran
select top(1) Name, 'first window' from TableOne with (xlock);
-- then wait for a while

-- open window 2 and run this
begin tran
SELECT top(1) Name, 'second window' from TableTwo with (xlock);
SELECT top(1) Name, 'second window' from TableOne with (xlock);
-- then wait for a while

-- back to window 1 and run this
select top(1) Name, 'first window' from TableTwo with (xlock);
-- wait for a while, a deadlock should happen

References:
https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlocks_2d00_in_2d00_sql_2d00_server.aspx

Template of a Stored Procedure with Savepoint

Savepoint is used for selective roll back. Using savepoint, a transaction can roll back to a selected location that has been marked. When it is rolled back, in the end the transaction must be completed by using 'commit transaction' or rolled back altogether.

Savepoint name should be unique even though duplicate is allowed. If a roll back is occurred where there is a duplicate, the transaction will be rolled back to the latest savepoint.

CREATE PROCEDURE [Procedure_Name]
AS
BEGIN

-- generate a unique savepoint name by appending procedure name (OBJECT_NAME(@@procid)) and nested level (@@nestlevel)
-- we could also use only @@nestlevel as it will always be unique in an active connection
-- savepoint name's maximum length is limited to 32 characters only
DECLARE @savepoint NVARCHAR(32) = CAST (OBJECT_NAME(@@procid) AS NVARCHAR(29)) +
           CAST (@@nestlevel AS NVARCHAR(3))

-- this is to check whether nested transactions exist when entering this procedure,
--  the value will be used later for checking condition
DECLARE @entryTrancount INT = @@trancount

BEGIN TRY
 BEGIN TRANSACTION
 SAVE TRANSACTION @savepoint
 
 --do something here
 
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 -- transaction is uncommittable (XACT_STATE() = -1) and no nested transactions exist (@entryTrancount = 0)
 IF XACT_STATE() = -1 AND @entryTrancount = 0
  ROLLBACK TRANSACTION
 -- otherwise if transaction is committable
 ELSE IF XACT_STATE() = 1    
  BEGIN
   ROLLBACK TRANSACTION @savepoint
   COMMIT TRANSACTION
  END
   
 DECLARE @ERROR_MESSAGE NVARCHAR(4000)
 SET @ERROR_MESSAGE = 'Error occured in procedure ''' + OBJECT_NAME(@@procid)
       + ''', Original Message: ''' + ERROR_MESSAGE() + ''''
 RAISERROR (@ERROR_MESSAGE, 16, 1)
 RETURN -100
END CATCH
END

According to MSDN, XACT_STATE function returns three values:
1 - The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
0 - There is no active user transaction for the current request.
-1 - The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.


References and further reading:
http://msdn.microsoft.com/en-us/library/ms188378%28v=SQL.105%29.aspx
Pro SQL Server 2008 Relational Database Design and Implementation - Louis Davidson
http://msdn.microsoft.com/en-us/library/ms189797.aspx
http://dosql.com/cms/index.php?option=com_content&view=article&id=101:trancount-and-xactstate&catid=40:microsoft-sql-server&Itemid=41

SQL Server Trigger Template

This a template for creating an After/Instead Of trigger in SQL Server 2005/2008:

CREATE TRIGGER <schema>.<tablename>$[InsteadOf]<actions>[<purpose>]Trigger
ON <schema>.<tablename>
[AFTER|INSTEAD OF] <comma delimited actions> AS
BEGIN

 DECLARE @rowsAffected INT,  --stores the number of rows affected
         @msg VARCHAR(2000)  --used to hold error message
 SET @rowsAffected = @@ROWCOUNT
 
 IF @rowsAffected = 0 RETURN
 
 SET NOCOUNT ON --to avoid the rowcount messages
 SET ROWCOUNT 0 --in case client has modified the rowcount
 
 BEGIN TRY
  --[validation section]
  
  --[modification section]

  --[perform action] --for INSTEAD OF trigger
 END TRY
 BEGIN CATCH
  IF @@trancount > 0
   ROLLBACK TRANSACTION
  
  --log the error
  EXECUTE utility.ErrorLog$insert  --this is only one example to do logging
  
  DECLARE @ERROR_MESSAGE NVARCHAR(4000)
  SET @ERROR_MESSAGE = ERROR_MESSAGE()
  RAISERROR (@ERROR_MESSAGE, 16, 1)
 END CATCH
END

Below is an example of using a table and a procedure to do error logging:

CREATE TABLE utility.ErrorLog(
 ERROR_NUMBER int NOT NULL,
 ERROR_LOCATION sysname NOT NULL,
 ERROR_MESSAGE varchar(4000),
 ERROR_DATE datetime NULL
  CONSTRAINT dfltErrorLog_error_date DEFAULT (getdate()),
 ERROR_USER sysname NOT NULL
  --use original login to capture the user name of the actual user
  --not a user that has been impersonated
  CONSTRAINT dfltErrorLog_error_user_name DEFAULT (original_login())
)
GO

CREATE PROCEDURE utility.ErrorLog$insert
(
 @ERROR_NUMBER int = NULL,
 @ERROR_LOCATION sysname = NULL,
 @ERROR_MESSAGE varchar(4000) = NULL
) AS
BEGIN
 BEGIN TRY
  INSERT INTO utility.ErrorLog(ERROR_NUMBER, ERROR_LOCATION, ERROR_MESSAGE)
  SELECT ISNULL(@ERROR_NUMBER, ERROR_NUMBER()),
      ISNULL(@ERROR_LOCATION, ERROR_MESSAGE()),
      ISNULL(@ERROR_MESSAGE, ERROR_MESSAGE())
 END TRY
 BEGIN CATCH
  INSERT INTO utility.ErrorLog(ERROR_NUMBER, ERROR_LOCATION, ERROR_MESSAGE)
  VALUES (-100, 'utility.ErrorLog$insert',
      'An invalid call was made to the error log procedure')
 END CATCH
END

Reference:
Pro SQL Server 2008 Relational Database Design and Implementation - Louis Davidson

Isolation Levels vs Read Phenomena

Isolation level	Dirty reads	Non-repeatable reads	Phantoms
Read Uncommitted	may occur	may occur	may occur
Read Committed	-	may occur	may occur
Repeatable Read	-	-	may occur
Serializable	-	-	-

Source: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Read_phenomena

Using Table Variable Inside a Loop

Be careful when using a table variable inside a loop. If it's not cleared, the rows inside the table variable might accumulate after each iteration. It seems that in T-SQL, a variable declared inside a loop is not automatically re-initialised.

declare @counter integer = 0
while @counter < 10
begin
	declare @tableVar table (code integer)

    -- to test, comment out this line
	delete @tableVar -- need to clear otherwise previous results will accumulate
	
	insert into @tableVar values (@counter)
	
	select * from @tableVar
	
	set @counter = @counter + 1	
end

Try Catch Template

Below is an example of an SQL Try Catch template:

BEGIN TRY 

	BEGIN TRANSACTION
	
	-- put the query here	
	
	COMMIT TRANSACTION
	
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
    
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

Note that the script in the Catch block checks if @@TRANCOUNT > 0 before doing ROLLBACK TRANSACTION. This means, only do rollback when there's at least one active transaction on the current connection. In other word, if it exists at least one BEGIN TRANSACTION that has not been committed yet (by using COMMIT TRANSACTION) on the current connection.

For more information about @@TRANCOUNT: http://msdn.microsoft.com/en-us/library/ms187967.aspx

Checking if a Column Does Not Exist on a Table

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')
BEGIN
	-- alter table or other query
	-- . . .
END

Declared Variable has NULL as Default Value!

When we declare a variable, by default it has NULL as its default value. So, need to be careful when checking the variable's value.

-- similar result for CHAR
DECLARE @varString VARCHAR(20)	
SELECT @varString
-- result is NULL

-- similar result for DECIMAL, FLOAT and DATETIME
DECLARE @varNumber INTEGER
SELECT @varNumber
-- result is NULL

Dropping a Function if Exists in Database

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'function_name') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION function_name
GO

or

IF object_id(N'function_name', N'FN') IS NOT NULL
    DROP FUNCTION function_name
GO

NOT IN Clause and NULL Values

When ANSI_NULLS setting is ON, be careful when using NOT IN clause if any of the values listed for the clause has NULL value. The scripts below will explain:

create table Table1 (x integer)
insert into Table1 Values (1)
insert into Table1 Values (2)
insert into Table1 Values (3)
insert into Table1 Values (4)

create table Table2 (x integer)
insert into Table2 Values (1)
insert into Table2 Values (Null)
insert into Table2 Values (2) 
insert into Table2 Values (5)

create table Table3 (x integer)
insert into Table3 Values (Null)
insert into Table3 Values (Null)
insert into Table3 Values (Null)

-- These scripts will not return any result (it would if ANSI_NULLS is OFF):
select * from Table1
where x NOT IN (select x from Table2)

select * from Table1 where x NOT IN (select x from Table3)


-- However, these ones are fine:
select * from Table1
where x IN (select x from Table2)

select * from Table1
where x IN (select x from Table3)


-- This one is also fine:
select * from Table2
where x NOT IN (select x from Table1)
-- (returns '5', however if ANSI_NULLS is OFF this would return 'NULL' and '5')

SET ANSI_NULLS ON/OFF

When SET ANSI_NULLS is ON, any kind of comparisons against a null value evaluate to UNKNOWN. This is the ISO standard. In this case, any comparison against a NULL value must use IS NULL or IS NOT NULL to return TRUE/FALSE value.

When SET ANSI_NULLS is OFF, a NULL value can be compared against another NULL value with usual comparison operator ( '=' or '<>' ).

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.

-- Create table t1 and insert values.
CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO


-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL

-- returns nothing
SELECT * FROM t1 
WHERE a = @varname

-- returns nothing
SELECT * FROM t1 
WHERE a <> @varname

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a IS NULL
GO


-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a = @varname

-- returns rows (0,1)
SELECT * FROM t1 
WHERE a <> @varname

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a IS NULL
GO


-- Drop table t1.
DROP TABLE t1

Reference:
http://msdn.microsoft.com/en-us/library/ms188048.aspx

SET QUOTED_IDENTIFIER ON/OFF

When this is set to 'ON', any string enclosed with double quotes ( “ ) is treated as a T-SQL Identifier (such as table name, procedure name or column name) and the T-SQL rules for naming identifiers will not apply to it. To define a normal string literal, enclose it with single quotes ( ' ).

When this is set to 'OFF', any string enclosed with either single quotes or double quotes will be treated as a literal.

The default behavior is 'ON' in any database.

Example:

SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SELECT "identity","order" 
FROM "select"
ORDER BY "order";
GO

Reference:
http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/

Further reference:
http://msdn.microsoft.com/en-us/library/ms174393.aspx

SET and SELECT Differences

- SET is the ANSI standard for variable assignment, SELECT is not.

- SELECT can be used to assign values to more than one variable at a time. SET can only assign a value to one variable at a time.

- When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

- When assigning a variable from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (the variable will have its' previous value if it has been assigned before).

- Be careful with SET and CASE WHEN statement. When any conditions do not match, CASE WHEN will return ‘NULL’ if ELSE is not specified. Therefore the variable will have a ‘NULL’ value.

DECLARE @errorCode AS INTEGER 
SET @errorCode = 0	
SET @errorCode = CASE
				 WHEN 'A'='B'
				 THEN 1000
				 END
SELECT @errorCode
-- @errorCode will return NULL

- Always use ‘SELECT’ instead of ‘SET’ to get @@ERROR and @@ROWCOUNT

SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR

Related article:
http://vyaskn.tripod.com/differences_between_set_and_select.htm

CASE WHEN Statement Examples

-- this is similar to ' SELECT * FROM Customers WHERE Country = 'France' '
SELECT * FROM Customers
WHERE CASE WHEN Country = 'France' THEN 1 END = 1

Nested CASE WHEN examples:

declare @test varchar(10);
set @test = 'debug';
SELECT  * FROM Customers
WHERE 1 = (	CASE 
		WHEN @test = 'debug'
		THEN	CASE WHEN Country = 'France' AND City = 'Marseille' 
				THEN 1 
				END  
		END )	

declare @test varchar(10);
set @test = 'debug';
SELECT  * FROM Customers
WHERE 1 = (	CASE 
			WHEN @test = 'debug' 
			THEN	CASE WHEN Country = 'France'  
					THEN	CASE
							WHEN  City = 'Marseille'
							THEN 1
							ELSE NULL
							END
					END  
			END )	

This one actually will give the same result as the previous query (2nd example on this article). Below is the result:

Using LIKE clause:

declare @test as varchar(50)
set @test = 'abcdef'
SELECT	CASE 
		WHEN @test LIKE '%bc%' THEN 1
		WHEN @test LIKE '%de%' THEN 2
		ELSE 0
		END	

The result is '1' because the first condition is the first match.

An example of further filtering the rows' cities given their countries:

SELECT * FROM CUSTOMERS
WHERE City = (	CASE WHEN Country = 'France' THEN 'Marseille'
					 WHEN Country = 'UK' THEN 'London'
					 WHEN Country = 'Spain' THEN 'Madrid'
					 END  )
ORDER BY Country

The result is:

Example of Using PARTITION Clause

SELECT ContactName, Country, COUNT(*) OVER (PARTITION BY Country) FROM Customers
ORDER BY Country

This will generate the same result as this query:

SELECT ContactName, Country, 
		(SELECT COUNT(*) FROM Customers C1 WHERE C2.Country = C1.Country) 
FROM Customers C2
ORDER BY Country