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:
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.
As a conclusion, for a small table it doesn't make any significant difference, but for large table it does. Happy coding!
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
SET NOCOUNT ON;
T.name AS TableName,
CT.name AS DataType,
sys.columns C, sys.tables T, sys.types CT
C.system_type_id = CT.system_type_id
AND C.object_id = T.object_id
AND c.name = @columnName
To find all tables that contain the column ArticleId, run it like this:
EXEC FindAllColumns 'ArticleId'
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.
-- Force an error here for testing purposes
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)