Click here to Skip to main content
11,480,938 members (62,246 online)
Click here to Skip to main content

Tagged as

COALESCE and ISNULL in SQL Server

, 1 Aug 2014 CPOL 7.5K 3
Rate this:
Please Sign up or sign in to vote.
COALESCE VS ISNULL in SQL Server - Part1

Introduction

COALESCE and ISNULL functions are used to return the first non-null expression among the input expression. Though these two functions look similar, there are certain differences. Let’s dive in to see the differences.

COALESCE and ISNULL

To understand the basic functioning of COALESCE and ISNULL, consider the following basic example:

In this case, both the functions return the same output of 1759. One advantage of COALESCE is that it supports multiple inputs.

Main differences include:

  1. COALESCE is ANSI Standard whereas, ISNULL is SQL Server Specific
  2. COALESCE can accept multiple parameters whereas, ISNULL can accept only two parameters

Data Type Considerations

The main difference between COALESCE and ISNULL is their difference in handling various data types.

The data type of a COALESCE expression is the data type of the input with the highest data type precedence. If all inputs are un-typed NULLs, then an error will be returned.

The data type of an ISNULL expression is the data type of the first input. If the first input is an un-typed NULL literal, the data type of the result is the type of the second input. If both inputs are the un-typed literals, then type of the output is INT.

Output

Now, consider the example:

Output

Notice that with COALESCE, regardless of which input is specified first, the type of the output is VARCHAR (10) - the one with higher precedence. With ISNULL, the type of the output depends on the first input. If the first input is VARCHAR (3), then the result will also be VARCHAR(3).

Now consider the second example:

Output

As the INT data type has precedence over VARCHAR data type, SQL Server tries to convert the value ‘abc’ to an INT which results in error.

Performance Impact

Generally COALESCE (V1, V2) is internally translated by SQL Server as:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

For testing the performance of COALESCE and ISNULL, I am creating a new table called T1 and populating with sample data as below:

Consider the following query and include the actual execution plan:

It returns a sum of 124750 and returns an execution plan like this:

Though the table T1 is being referred only once, due to the COALESCE function, the table is scanned twice.

Now, rewrite the query as follows:

See the execution plan.

Note that this differentiation exists only in sub queries, while in normal queries both behave similarly.

Means:

Both the statements behave similarly with no performance differentiation.

Summary

In summary, please understand the following:

  1. COALESCE and ISNULL are the two functions that will return a NON-NULL value instead of a NULL
  2. The data type of the output returned by COALESCE will be the data type with highest precedence, whereas data type of the ISNULL output will be the data type of the first input.
  3. COALESCE is ANSI-SQL Standard and can accept multiple parameters
  4. As far as the performance of the query is concerned, ISNULL is the preferable choice in subqueries
  5. I am going to write my second article on the same which will give you an insight into the impact of COALESCE and ISNULL in transactions.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Uppuluri Aditya
Software Developer (Junior)
India India
I have started my IT career in April 2013. Currently working as C#, SSIS and SQL Server developer in a multinational company. I am a Microsoft Certified Professional in SQL Server 2012.
Follow on   LinkedIn

Comments and Discussions

 
SuggestionNumber of arguments and few other suggestions Pin
Mika Wendelius31-Jul-14 18:50
mentorMika Wendelius31-Jul-14 18:50 
GeneralRe: Number of arguments and few other suggestions Pin
Uppuluri Aditya1-Aug-14 1:45
memberUppuluri Aditya1-Aug-14 1:45 
Suggestion[My vote of 1] Not enough for a basic codeproject.com standard artile Pin
Md. Marufuzzaman31-Jul-14 12:21
mentorMd. Marufuzzaman31-Jul-14 12:21 
GeneralRe: [My vote of 1] Not enough for a basic codeproject.com standard artile Pin
Uppuluri Aditya1-Aug-14 1:46
memberUppuluri Aditya1-Aug-14 1:46 
GeneralRe: [My vote of 1] Not enough for a basic codeproject.com standard artile Pin
sx20082-Aug-14 0:47
membersx20082-Aug-14 0:47 
SuggestionRe: [My vote of 1] Not enough for a basic codeproject.com standard artile Pin
Md. Marufuzzaman4-Aug-14 5:20
mentorMd. Marufuzzaman4-Aug-14 5:20 
GeneralRe: [My vote of 1] Not enough for a basic codeproject.com standard artile Pin
vikramsagar5-Aug-14 1:40
membervikramsagar5-Aug-14 1:40 
AnswerRe: [My vote of 1] Not enough for a basic codeproject.com standard artile Pin
Md. Marufuzzaman6-Aug-14 7:04
mentorMd. Marufuzzaman6-Aug-14 7:04 
GeneralRe: [My vote of 1] Not enough for a basic codeproject.com standard artile Pin
Uppuluri Aditya6-Aug-14 20:27
memberUppuluri Aditya6-Aug-14 20:27 
GeneralRe: [My vote of 1] Not enough for a basic codeproject.com standard artile Pin
Md. Marufuzzaman7-Aug-14 11:03
mentorMd. Marufuzzaman7-Aug-14 11:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 1 Aug 2014
Article Copyright 2014 by Uppuluri Aditya
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid