There are a great many articles out there expounding the virtues of using the ANSI standard COALESCE function over the non-standard IS NULL function. Indeed even the SQL Server MVP’s and SQL Server PSS have weighed in on the issue showing that in most cases the plans produced by the COALESCE and IS NULL functions are identical.
Let’s take a closer look. The TSQL below runs on the standard AdventureWorks database with one statement using the COALESCE function and the other using IS NULL:
As many articles have shown, the query plans produced do indeed seem virtually identical and the query costs are estimated to be exactly the same. However, as I have noted in previous posts, there is no substitute for actually running a real-world comparison several hundred times in order to see what transpires. Whenever I run comparisons like this I always like to raise the thread priority of SQL Server so that activities from other services and programs don’t cause inconsistent results:
Here are those same 2 TSQL statements encapsulated in loops to run and time their execution:
The results below show that IS NULL consistently bests COALESCE by ~18% in execution speed.
If order to remove any doubt in the following TSQL I have switched the running order of the 2 queries and additionally introduced actual IO into the equation by performing a DBCC DROPCLEANBUFFERS every 10th iteration, to approximate a 90% cache hit ratio.
As the results show, IS NULL still consistently bests the performance of COALESCE by a substantial margin.
Now if this TSQL statement is just a once-off then switching to using COALESCE is probably no big deal. However if the statement is in a batch, trigger or stored procedure which is executed hundreds of thousands of times every hour on a production server then the change will significantly impact performance.
As I have shown, IS NULL is often significantly faster than COALESCE especially when used on an indexed field. Therefore before making your TSQL ANSI compliant just because the articles out there suggest that you should and switching all IS NULL occurrences toCOALESCE functions you would be well advised to test the performance of the two functions in a real world scenario as I have shown in order to directly compare their run-times. Looking exclusively at execution plans does not show the whole story.
These tests were performed on SQL Server 2008 SP1