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