Does wrapping nullable columns in ISNULL cause table scans?

Question

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?

Ulyssesumayyad

Answer 1

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

If your code compares two NULL values or a NULL value with any other value, your code will return an unknown result.

Whilst the expression itself does evaluate to unknown your code returns a completely deterministic result once you understand that any =, <>, >, < etc comparison with NULL evaluate as Unknown and that the WHERE clause only returns rows where the expression evaluates to true.

It is possible that they mean if ANSI_NULLS is off but the example they give in the documentation of WHERE ISNULL([c2],0) > 2; vs WHERE [c2] > 2; would not be affected by this setting anyway. This setting

affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL.

Execution plans showing scans vs seek or below

CREATE TABLE #foo
  (
     x INT NULL UNIQUE
  )

INSERT INTO #foo
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM   sys.all_columns

SELECT *
FROM   #foo
WHERE  ISNULL(x, 10) = 10

SELECT *
FROM   #foo
WHERE  x = 10

SELECT *
FROM   #foo
WHERE  x = 10
        OR x IS NULL 

Bevash

What's that syntax in the ORDER BY clause? (I've googled but it's somewhat hard to google for...) – Sacramental

@Sacramental - It is the least typing way I have found so far of ordering by a constant in order to just get an ascending sequence of numbers. over (order by(SELECT 0)) or over (order by @@spid) are alternatives. You can't use a literal constant value as SQL Server gives you an error. The divide by zero prevents it being treated as a literal constant. You can't use 1/0 as then SQL Server things you are trying to order by a integer column ordinal and throws an error. – Bevash

Oh I see, $ is interpreted as a money literal with value zero! That's surprising generosity from the parser, I have to say :) – Sacramental

AakashM: SELECT SQL_VARIANT_PROPERTY($,'BaseType') – Darbie

@MartinSmith With apologies for the thread necromancy, but can you elaborate on the execution-plan for the 3rd query? Why does such a long chain of operations get added just by adding OR x IS NULL? What is the full significance of the Constant Scan + Compute Scalar + Concatenation + Sort + Merge? Is it a "cheap" execution plan? Why isn't SQL Server smart enough to infer the intent of the first query is the same as the 3rd and use the same execution-plan? (I ran it against SQL Server 2019 and the plans are identical to those in your screenshot which I assume was 2008 R2 or 2012. – Compliancy

If you do SELECT * FROM #foo WHERE x = 10 OR x = 9 you see a much simpler looking plan with one seek operator and two seek predicates, But SQL Server can't combine the two types of equality IS and EQ in the same seek operator in the same way. It uses this "dynamic seek" approach instead – Bevash