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
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
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
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
You can read the full discussion and check out the plans here.