Anatoly Lubarsky Logo
programming, design, integration, games, music

Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR

Suppose, you have to search the database table by optional parameters. There are 3 ways: COALESCE trick, just the same ISNULL trick(the most popular one) and the optimized ISNULL. Which one to choose? I have made some tests.


COALESCE


DECLARE @field1Param CHAR(4)
DECLARE @field2Param INT

SET @field1Param = NULL
SET @field2Param = NULL

SELECT *
  FROM sales_big
 WHERE stor_id = COALESCE(@field1Param, stor_id) 
   AND sales_id = COALESCE(@field2Param, sales_id)

ISNULL


DECLARE @field1Param CHAR(4)
DECLARE @field2Param INT

SET @field1Param = NULL
SET @field2Param = NULL

SELECT *
  FROM sales_big
 WHERE stor_id = ISNULL(@field1Param, stor_id) 
   AND sales_id = ISNULL(@field2Param, sales_id)

IS NULL OR...

DECLARE @field1Param CHAR(4)
DECLARE @field2Param INT

SET @field1Param = NULL
SET @field2Param = NULL

SELECT *
  FROM sales_big
 WHERE ((@field1Param IS NULL) OR (stor_id = @field1Param)) 
   AND ((@field2Param IS NULL) OR (sales_id = @field2Param))

These are test results: I used the table of 1600000 rows. Used 4 queries in each case. The search was processed according to 2 indexed fields: one of them was of lower cardinality, than the other.


Subtree cost:


  1. 15.990372 (COALESCE + 1.1%).
  2. 14.978022 (ISNULL + 0.1%).
  3. 14.976111 (IS NULL OR - the winner).

COALESCE time:


  1. both fields are not null ~ 7-8 sec.
  2. one of the fields is null ~ 8 sec and 7 sec.
  3. both null ~ 52-53 sec.

ISNULL time:


  1. both fields are not null ~ 7-8 sec.
  2. one of the fields is null ~ 7 sec in both cases.
  3. both null ~ 52 sec.

IS NULL OR time:


  1. both fields are not null ~ 7 sec.
  2. one of the fields is null ~ 7 sec in both cases.
  3. both null ~ 49 sec.

As you can see - the best performer is IS NULL OR case, while the difference between all 3 of them is minor. Enjoy :)


Related Posts:

Monday, March 1, 2004 3:59 AM

Comments

# re: T-SQL: coalesce/isnull/(A is null) or (B)
Thanks for outlining the three approaches so clearly. I think the matter bears further study, actually, since many people will take this approach rather than write many SPs to cover each case. We seem to have better luck with the Coalesce method. I'll make a note here if we find anything else, though.

5/11/2004 11:48 PM by dan nordquist

# re: T-SQL: coalesce/isnull/(A is null) or (B)
dan:

Good, I will also investigate it further.

5/12/2004 12:27 AM by Anatoly Lubarsky

# re: ISNULL vs COALESCE speed test

11/25/2004 1:17 AM by Go with the flow

# re: IsNull vs Coalesce Speed test

11/25/2004 1:19 AM by Go with the flow

# ISNULL or COALESCE

11/29/2004 11:45 AM by Vinod Kumar's Blog

# re: T-SQL: coalesce/isnull/(A is null) or (B)
A good read. Just a quick point worth mentioning: both the coalesce and isnull methods will NOT return rows whose columns contain NULL values (unless ANSI_NULLS is set to off).

12/1/2004 1:24 AM by Jacob Norbut

# re: T-SQL: coalesce/isnull/(A is null) or (B)
http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html

12/2/2004 3:07 PM by Roji's SQL Blog

# MSSQL: Paul McBride on ISNULL vs. COALESCE

3/17/2005 11:55 PM by Anatoly Lubarsky: Weblog

# re: Howwould you do this - Solution

3/27/2005 12:02 AM by Bill's House O Insomnia

# T-SQL: coalesce/isnull/(A is null) or (B) [From Anatoly Lubarsky]

3/27/2005 1:44 AM by Bill's House O Insomnia

# T-SQL: Coalesce vs. IsNull

4/22/2005 11:11 PM by Little Tidbits of Random Knowledge

# Performance: ISNULL vs. COALESCE
Originally posted here.
Mladen aka spirit1 posted a speed test of COALESCE vs. ISNULL. Reported result:...

7/13/2006 8:17 AM by Adam Machanic

# re: Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR
performant, noun: a performer

10/19/2006 8:58 PM by Arnie Rowland

# re: Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR
Arnie: thanks for the tip...

10/28/2006 5:52 AM by Anatoly Lubarsky

# re: Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR
The real question is not which one is faster, but which one is part of the SQL standard. The answer is Coalesce.
When writing code you should always adhere to the ANSI standards whenever possible. It could make your or someone else's life easier down the road.

12/14/2006 1:33 AM by Mark Lyczewski

# re: Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR
Mark: the question here was not standards.

Anyway, speaking of writing code, ISNULL is more readable and is a part of T-SQL. BOL does not say anything about use of ISNULL.

You remind me those religious people that have standards instead of bible. Here we speak about T-SQL (not SQL).

12/14/2006 3:21 AM by Anatoly Lubarsky

# Performance: ISNULL vs. COALESCE
Originally posted here . Mladen aka spirit1 posted a speed test of COALESCE vs. ISNULL. Reported result:

1/8/2007 10:38 PM by Adam Machanic

# re: Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR
did you clear the buffercache and checkpoint before each run??

8/7/2008 6:31 PM by alex

# How to SUM a Sql Column that has a NULL value. « Jethro Badenhorst
How to SUM a Sql Column that has a NULL value. « Jethro Badenhorst

8/5/2011 5:41 AM by Pingback/TrackBack

# Top Answers to 5 Burning Questions on SQL COALESCE Function – {coding}Sight
Top Answers to 5 Burning Questions on SQL COALESCE Function – {coding}Sight

1/26/2021 6:58 AM by Pingback/TrackBack

# Top Answers to 5 Burning Questions on COALESCE Function in SQL Server - {coding}Sight
Top Answers to 5 Burning Questions on COALESCE Function in SQL Server - {coding}Sight

6/16/2021 2:25 AM by Pingback/TrackBack

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(01) September 2019
(02) January 2018
(01) June 2013
(03) March 2013
(02) February 2013
(01) July 2012
(01) April 2012
(01) September 2011
(01) August 2011
(03) May 2011
(01) December 2010
(01) November 2010
(01) October 2010
(01) June 2010
(01) May 2010
(02) March 2010
(01) January 2010
(02) December 2009
(03) September 2009
(03) August 2009
(09) July 2009
(04) June 2009
(03) May 2009
(02) April 2009
(03) March 2009
(02) February 2009
(02) January 2009
(04) December 2008
(04) November 2008
(05) October 2008
(04) September 2008
(05) August 2008
(04) July 2008
(05) June 2008
(07) May 2008
(04) April 2008
(03) March 2008
(02) February 2008
(03) January 2008
(03) December 2007
(05) November 2007
(04) October 2007
(05) September 2007
(12) August 2007
(11) July 2007
(14) June 2007
(13) May 2007
(13) April 2007
(10) March 2007
(11) February 2007
(14) January 2007
(14) December 2006
(12) November 2006
(08) October 2006
(09) September 2006
(06) August 2006
(08) July 2006
(10) June 2006
(09) May 2006
(22) April 2006
(25) March 2006
(12) February 2006
(14) January 2006
(19) December 2005
(17) November 2005
(16) October 2005
(16) September 2005
(12) August 2005
(14) July 2005
(09) June 2005
(12) May 2005
(12) April 2005
(20) March 2005
(11) February 2005
(12) January 2005
(18) December 2004
(13) November 2004
(12) October 2004
(14) September 2004
(09) August 2004
(23) July 2004
(19) June 2004
(29) May 2004
(19) April 2004
(16) March 2004
(09) February 2004
(06) January 2004
(02) December 2003
(01) November 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs