Category: SQL

SQL: Recompile after DB restore

I have recently come to realise an issue with database restoring in SQL Server 2005.

Depending on how the database is restored from another database, there are chances for some stored procedures to stop working (AFAIK this could be random), because their execution plan isn’t relevant anymore. The typical symptom is that when you run a stored proc, it simply sticks in the running state forever. This requires recompilation of the stored proc.

Recompiling stored procs is quite simple. This can be setup as automatic SQL jobs after each restore to prevent broken stored procs.

Recompiling all stored procs that access a key table

EXEC sp_recompile 'table_name';

Otherwise we can also target a specific stored proc

EXEC sp_recompile 'sp_name';

If identifying these stored procs is not realistic (due to its randomness), we can also consider recompiling all DB objects by using sp_MSforeachtable

EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";

What sp_recompile does is simply marking the target object for recompilation. The objects will be recompiled when the next time it is accessed. Even though, the last option could take a long time because it loops through each table in the DB and recursivly marks all objects that depends on their recursive parent objects for recompilation.


SQL Search: Quickly find your database objects

I don’t know how other DB guys dealt with this problem but I get frustrated looking for the stored procedure Shopfront_Readonly.usp_DataReader_GetFirstXProductWithSixDigitBarcodeAndRelatedCustomerInformation among a thousand other procs all having the same schema and prefix.

Why not download SQL Search from Red Gate and start searching DB objects like searching in the Windows 7 start menu. SQL Search has to be the best free add-on to SQL Management Studio. It indexes your database for once and search whatever you need in seconds. It’s as easy as installing it and press Ctrl + Alt + D or click the SQL Search button SQL Search Button to bring it up.

SQL Search

T-SQL: CASE doesn’t go well with NULL

I recently came across a situation that requires the following simple case:

SELECT CASE Len(Article.Title)
WHEN 0 THEN 'No Title'
ELSE Article.Title END
AS Title

This will not work if the article title is a NULL value, an even simpler test can easily illustrate the problem:

ELSE 'Not null' END

This will always return ‘Not null’. The CASE statement simply doesn’t work with NULL values. What I did to get around this problem is by using the Isnull function:

WHEN 0 THEN 'Null'
ELSE 'Not null' END

Update: jba has pointed out a critical performance issue with this method. The ISNULL function will be evaluated for each row of data that the query is processing. This can effectively turn any index-able query to use a table scan. Thanks for the tip John 🙂