Every SQL DBA and developer should read the blog of MVP Erland Sommarskog – but particularly  his article on dynamic search conditions in T-SQL. I’ve linked above to his SQL 2005 article but his 2008 version is also a must-read.

I seem to regularly come across uses of the SQL in the title above… Erland’s article explains in detail why this is inefficient, but I came across a nice example recently…

A stored procedure contained the following code :-

WHERE @Name is null or [Name] like @Name

as a nonclustered index exists on the Name column, you might assume this would be handled efficiently by SQL Server.

However, I got the following output from SET STATISTICS IO

Table 'xxxxx'. Scan count 15, logical reads 47760, physical reads 9, read-ahead reads 13872, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note the high number of logical reads…

After a bit of investigation, we found that @Name could never actually be set to NULL in this particular example. ie the @x IS NULL was spurious…

So, we changed the call to WHERE  [Name] like @Name

Now, how much more efficient is this code ?

Table 'xxxxx'. Scan count 3, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0

A nice easy win in this case…… a full index scan has been replaced by a significantly more efficient index seek.

I managed to recreate the same behaviour on Adventureworks – here’s a quick query to demonstrate :-

USE adventureworks
SET STATISTICS IO ON
DECLARE @id INT = 51721
SELECT * FROM Sales.SalesOrderDetail WHERE @id IS NULL OR salesorderid = @id
SELECT * FROM Sales.SalesOrderDetail WHERE salesorderid = @id

Take a look at the STATISTICS IO output and compare the actual query plans used to prove the impact of  WHERE @id IS NULL.

And just to follow some of Erland’s advice – here’s how you could get similar performance if it was possible that @id could actually sometimes contain NULL.

DECLARE @sql NVARCHAR(4000), @parameterlist NVARCHAR(4000)
DECLARE @id INT = 51721 – or change to NULL to prove query is functionally correct
SET @sql = 'SELECT * FROM Sales.SalesOrderDetail WHERE 1 = 1'
IF @id IS NOT NULL SET @sql = @sql + ' AND salesorderid = @id'
IF @id IS NULL SET @sql = @sql + ' AND salesorderid IS NULL'
SET @parameterlist = '@id INT'
EXEC sp_executesql @sql, @parameterlist,@id

Sometimes I think we focus too much on hardware and SQL Server configuration – when really the answer is focus on writing efficient SQL.