Working with large tables in SQL Server

by Andrei Hetel 1. June 2013 18:56

Example of a bad written query, take a look at the snippet below. At the time it was written the table size was very small couple of hundred records maybe. On the RequestedPage field there is clustered index.

DECLARE @description NVARCHAR(MAX)
DECLARE @ArticleLinkId INT
-- Obtain the value for @description variable using a simple process - not important here
SELECT @ArticleLinkId = LinkId FROM PageCollection with (FORCESEEK)  WHERE RequestedPage = @description

Query plan look like in the following picture:

Index Scan

That 'simple' operation took over 30 seconds to complete. Please note by looking at 'Estimated Number of Rows' that the table has more 29 million records. It took some time until I realized that the data type of @description variable was NVARCHAR(MAX) and only after I tried to use FORCESEEK hint (which of course didn't work).

By simply changing the data type to VARCHAR(255) which is also the data type of RequestedPage column everything start to fly, see picture below.

Index Seek

As a conclusion, for a small table it doesn't make any significant difference, but for large table it does. Happy coding!

Usefull SQL Server script

by Andrei Hetel 23. March 2013 16:21

It happen to me a lot of time to forget some relations that I have in my databases. Instead of following the foreign keys and do a lot of clicking, I prefer to run this stored procedure.

 

CREATE PROCEDURE FindAllColumns
(@columnName VARCHAR(255))
AS
BEGIN

	SET NOCOUNT ON;
	
	SELECT 
		T.name AS TableName,
		C.name,
		CT.name AS DataType,
		C.max_length,
		C.precision,
		C.scale
	FROM	 
		sys.columns C,  sys.tables T, sys.types  CT
	WHERE 
		C.system_type_id = CT.system_type_id 
		AND C.object_id = T.object_id 
		AND c.name = @columnName

END 

To find all tables that contain the column ArticleId, run it like this:

 

EXEC FindAllColumns 'ArticleId'

SQL Server error handling

by Andrei Hetel 22. October 2012 14:50

Starting with SQL Server 2005 the old error handling was improved with TRY CATCH block. That was a significant step forward. I wrote this post mostly to remember how to output all the details about the error message, keep forgetting.

BEGIN TRY
        -- Force an error here for testing purposes
        SELECT 1/0
END TRY

BEGIN CATCH
    PRINT 'sql server error number: ' + CAST(ERROR_NUMBER() AS VARCHAR)
    PRINT 'sql server error message: ' + ERROR_MESSAGE()
    PRINT 'sql server error severity:' + CAST(ERROR_SEVERITY()  AS VARCHAR) 
    PRINT 'sql server error state:' +  CAST(ERROR_STATE() AS VARCHAR)
    PRINT 'sql server error procedure:' + ERROR_PROCEDURE()
    PRINT 'sql server error line:' + CAST(ERROR_LINE() AS VARCHAR)
END CATCH