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.
