SQL Server Performance Improvement Best Practices: ISNULL

Recently, we have started rechecking SQL queries of our one of client’s project. It’s basically performance improvement of SQL queries. While rechecking queries, we noted some best practices about to write SQL query.

ISNULL(). As you know this function is used to set value in case of NULL value found. Do you ever think that sometime this function impact performance? Yes, It impacts if you use in left hand site in WHERE clause. Here we put some statistics to measure.

Error when loading gists from https://gist.github.com/.
select  *
from    (
             select  NULL as FieldStatus
             union all
             select 1 as FieldStatus
             union all
             select 20 as FieldStatus
             union all
             select NULL as FieldStatus
             union all
             select 0 as FieldStatus
             union all
             select 0 as FieldStatus
        ) as T1
where   isNull (T1 .FieldStatus ,0 ) = 20

In above query, you can see we used ISNULL function in left side in WHERE clause and you can also notice ESTIMATED SUB TREE COST of query. Now see below query with plain condition in left side.

Error when loading gists from https://gist.github.com/.
select  *
from    (
             select  NULL as FieldStatus
             union all
             select 1 as FieldStatus
             union all
             select 20 as FieldStatus
             union all
             select NULL as FieldStatus
             union all
             select 0 as FieldStatus
             union all
             select 0 as FieldStatus
        ) as T1
where   T1. FieldStatus = 20

You can measure ESTIMATED SUB TREE COST of both above queries. In these example, you noticed very less and ignorable difference but in case of million rows you will be notice huge different and also may MEMORY GRANT comes in picture.

We can do two things for best practices.

1. Make a field not null in table.
2. If it is essential to allow null then make condition in query like.
   1. in above query we set ZERO in case of NULL so put condition if FieldStatus is ZERO then add condition with isnull or in rest case put plain condition. This formula works for us.

Moreover, we can use OR condition in order to check condition but OR operate also impact performance. 

Hope you gain something over here. If you have any additional tips regarding this please comment.