Pages

Saturday 17 August 2013

Using isnull in where clause is expensive in SQL Server

The statement was similar to this :
(This is just an example)
Select * from CustomerMaster where StateId = isnull(@StateID, [StateId]);
After replacing isnull with ‘Case’  like below, the statement executed faster and the stored procedure ran in few milli seconds.
Select * from CustomerMaster where StateId = (Case @StateID when null then [StateId] else @StateID End);