Using isnull in where clause is expensive in SQL Server

Today I had to work on a stored procedure which was taking more than 20 seconds to run. On analyzing the stored procedure I found the cause of the slowness is due to the usage of isnull in where clause. The statement was similar to this :

Example

Select * from CusomerMaster where StateId = isnull(@StateID, [StateId]);

After replacing isnull with ‘Case’  like below, the statement executed faster and the stored procedure ran in few mille seconds.

Select * from CusomerMaster where StateId = (Case @StateID when null then [StateId] else @StateID End);

I know that using ‘case’ in where clause itself is expensive and it’s not a recommended performance suggestion. But in my scenario ‘Case’ is better than isnull. I’m not sure why case is better than isnull in my situation.

Reference

  • About IsNull in MSDN.

1 comment for “Using isnull in where clause is expensive in SQL Server

  1. March 25, 2015 at 8:51 am

    This is a very interesting observation. I’d like to add something. Using “CASE WHEN” clause has the same behavior as ISNULL (stops using indexes), however using “CASE @variable WHEN” clause makes use of indexes, as if it wasn’t there. This distinct difference is not to be forgotten. Thank you!

Leave your thoughts...