Bharat Patel Feb,08 2017
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.
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.
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.
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.