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 01, 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

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

(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