Friday 26 August 2011

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

Friday 19 August 2011

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

Friday 12 August 2011

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

Friday 5 August 2011

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