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
EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";
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.
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 to bring it up.
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
This will not work if the article title is a
NULL value, an even simpler test can easily illustrate the problem:
SELECT CASE NULL
WHEN NULL THEN 'Null'
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:
SELECT CASE Isnull(NULL, 0)
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 🙂