Performance implications of using ISNULL vs IS NULL

~ franklinyamamoto

Quite often I see queries that are not sargable due to the use of the ISNULL function in the query’s predicate (WHERE clause).  If you’re not familiar with sargable it means whether or not a condition in the predicate can take advantage of an index.  I won’t dive too deep into sargability here, but I’ll go into why using the ISNULL function in the predicate can prevent you from taking advantage of an index.

The demo query

This is the query we will use to demonstrate the problem with the ISNULL function:
select CustomerID
,NameStyle
,Title
,FirstName
,MiddleName
,LastName
,Suffix
,CompanyName
,SalesPerson
,EmailAddress
,Phone
,PasswordHash
,PasswordSalt
,rowguid
,AccountNbr
,ModifiedDate from CustomerAccount where isnull(AccountNbr, '')=''

This query is looking for any records where the AccountNbr column is empty or NULL.  From looking at the execution plan we’ll see that there is a scan on the AccountNbr column:

This query, based on estimates will return one row but according to the plan it is reading about 3.6 million rows to find this.  This version of the query takes 9 seconds on average to execute.

In general applying a function to a column in a predicate (where clause) causes an index to be unusable (unsargable).  This is not always the case, but most of the time it works this way.  In many cases queries that have unsargable predicates can be rewritten to make them sargable.

Rewritten query (and now it’s sargable)

It is recommended that if you are looking for NULL values you should use the IS NULL operator instead of the ISNULL function.  Don’t just take my word for it, Microsoft suggests it in their documentation as well: https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-ver15.

Here is the query where it was rewritten to use IS NULL.

select CustomerID
,NameStyle
,Title
,FirstName
,MiddleName
,LastName
,Suffix
,CompanyName
,SalesPerson
,EmailAddress
,Phone
,PasswordHash
,PasswordSalt
,rowguid
,AccountNbr
,ModifiedDate from CustomerAccount where AccountNbr IS NULL OR AccountNbr=''

You can see the query now shows an index seek using an index on the AccountNbr column:

This version of the query takes 0.001 seconds on average to execute.

In this case there was an index on the column that had the unsargable predicate; however, in the case that there isn’t an index the optimizer will probably start pointing out missing indexes.

Is the ISNULL function all that bad

No, ISNULL is meant to be used in places such as in the selected columns.  This function is meant to simplify case logic.  This is an example of a proper use of the ISNULL function:

Final thoughts

As usual your mileage may vary with using IS NULL vs ISNULL in your queries; however, it is a best practice to not do things that prevent a query from being sargable if at all possible.