0

The case for ISNULL

Recently I wrote a function for a report with a CASE block to take in a start and end date, and if the end date was NULL, to use the start date.

WHERE theDate BETWEEN @dateStart AND 
    CASE WHEN @dateEnd IS NULL
         THEN @dateStart
         ELSE @dateEnd
    END

However, when NULL was passed for the end date, the function would hang and not return anything.

What was happening was that estimated rows when passing a NULL using the CASE block created an unequal JOIN predicate. The estimated rows for CASE showed only 79 rows, while ISNULL showed a more accurate 28,393 rows.

The difference between using CASE and ISNULL is that CASE* will return the data type of the expression with the highest data type precedence. Using ISNULL returns the same data type as the expression being checked.

* COALESCE is equivalent to the CASE block.

From Paul White:

COALESCE( expression [ ,...n ] ) returns the data type of the expression with the highest data type precedence.

ISNULL(check_expression, replacement_value) returns the same type as check_expression.

When comparing a single expression, use ISNULL. It also performs better than COALESCE.

You can read the full discussion and check out the plans here.

Norbert Krupa

Technical Consultant

Leave a Reply

Your email address will not be published.