SQL Refactoring #1: CONCAT_NULL_YIELDS_NULL

During the peak hours we have been seeing lot of blocking by one Stored Procedure. I looked into the master.dbo.sysprocesses table and it showed that this was getting blocked because it was waiting on an exclusive lock to recompile the Stored Procedure.

Why would this Stored Procedure get recompiled? I ran a trace with SP:Recompile switch on, and saw that it was recompiling on the statement

SET CONCAT_NULL_YIELDS_NULL OFF

What does CONCAT_NULL_YIELDS_NULL do? According to MSDN, this option controls whether concatenation results are treated as null or empty string values. When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

According to a KB articles, Troubleshooting stored procedure recompilation and SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile, I found that if you execute the SET statement to set the options to OFF, the stored procedure will be recompiled every time it runs, causing performance problems. The reason is that by default CONCAT_NULL_YIELDS_NULL is set to ON in the SQL Server, and changing the option in a Stored Procedure triggers the recompilation.

Therefore do not set CONCAT_NULL_YIELDS_NULL inside of the stored procedure. The recommended solution is to use the ISNULL function when concatenating strings.

For example, don't do this:

SELECT * FROM mytable WHERE name LIKE 'Xavier ' + @lastname + '%'

Instead do this:

SELECT * FROM mytable WHERE name LIKE 'Xavier ' + ISNULL(@lastname,'') + '%'