COALESCE vs. ISNULL vs. IS NULL OR | TSQL

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.
   one of the fields is null ~ 8 sec and 7 sec.
   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.
   one of the fields is null ~ 7 sec in both cases.
   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.