Code analysis rule SR0007 for Visual Studio 2010 database projects states that:
You should explicitly indicate how to handle NULL values in comparison expressions by wrapping each column that can contain a NULL value in an ISNULL function.
However code analysis rule SR0006 is violated when:
As part of a comparison, an expression contains a column reference ... Your code could cause a table scan if it compares an expression that contains a column reference.
Does this also apply to ISNULL, or does ISNULL never result in a table scan?
Yes it causes table scans. (though seems to get optimised out if the column isn't actually nullable)
The SR0007 rule is extremely poor blanket advice as it renders the predicate unsargable and means any indexes on the column will be useless. Even if there is no index on the column it might still make cardinality estimates inaccurate affecting other parts of the plan.
The categorization of it in the
Microsoft.Performance
category is quite amusing as it seems to have been written by someone with no understanding of query performance.It claims the rationale is
Whilst the expression itself does evaluate to
unknown
your code returns a completely deterministic result once you understand that any=
,<>
,>
,<
etc comparison withNULL
evaluate asUnknown
and that theWHERE
clause only returns rows where the expression evaluates totrue
.It is possible that they mean if
ANSI_NULLS
is off but the example they give in the documentation ofWHERE ISNULL([c2],0) > 2;
vsWHERE [c2] > 2;
would not be affected by this setting anyway. This settingExecution plans showing scans vs seek or below