Developping for the translation industry RSS 2.0

 Wednesday, February 24, 2010

The SQL Server functions ISNULL and COALESCE seem to occupy the same space in terms of functionality. The only difference is that ISNULL is restricted to only two parameters, while COALESCE can take any number of parameters. So why ever use ISNULL?

First of all, the simple answer for why to prefer ISNULL over COALESCE when given the choice is that ISNULL tends to produce query plans that are more efficient than COALESCE's. Examine the query plans for the two queries given below to see the difference:

SELECT a.au_id, 
((SELECT price
               FROM   titles
               WHERE  title_id = ta.title_id),0)
FROM   authors a
       JOIN titleauthor ta
         ON a.au_id = ta.au_id


SELECT a.au_id, 
       Coalesce((SELECT price
                 FROM   titles
                 WHERE  title_id = ta.title_id),0)
FROM   authors a
       JOIN titleauthor ta
         ON a.au_id = ta.au_id

The first query uses one less nested loop in it’s execution plan than the second, resulting in a lower overall cost. Note that I would never recommend writing queries that nest subqueries within functions. This example was only intended to show you the difference between the two generated execution plans, not to recommend a certain T-SQL coding style.

The other bit that most people don't know about ISNULL and COALESCE is that the return data type for ISNULL is guaranteed to be the same as the data type of the first parameter. However, the return data type of COALESCE is determined by data type precedence rules (see the Books Online topic "Data Type Precedence"). Therefore, the following queries will produce two different outputs:

DECLARE  @Example CHAR(2)
SET @Example = NULL

SELECT Isnull(@Example,'abcde'),

In this example, the first expression using ISNULL will return 'ab', which is the declared datatype of the first parameter, a char(2). The second expression using COALESCE will return the highest precedence data type, which is the longer string 'abcde.'

To summarize:

  • When you only need to coalesce two arguments, use ISNULL instead because it performs better.
  • When using COALESCE, you may want to use explicit casting to ensure you get a consistent return data type.

Other posts:

5 ways to generate random numbers with a T-SQL query

How to remove leading zeros from the results of an SQL Query

How to get the list of object modifications in SQL Server

How to use derived tables to boost SQL performance

Good Joke on SQL Injection

Wednesday, February 24, 2010 10:15:13 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL

About the author/Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2019
Stanislas Biron
Sign In
Total Posts: 135
This Year: 0
This Month: 0
This Week: 0
Comments: 1
All Content © 2019, Stanislas Biron