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:

• MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEED
• Why app roles are bad in MSSQL

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.

dan:

Good, I will also investigate it further.

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).

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

performant, noun: a performer

Arnie: thanks for the tip...

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.

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).

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

did you clear the buffercache and checkpoint before each run??

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

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

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