在ISNULL中包装可为空的列会导致表扫描吗? [英] Does wrapping nullable columns in ISNULL cause table scans?

问题描述

针对Visual Studio 2010数据库项目的代码分析规则 SR0007 指出: :

Code analysis rule SR0007 for Visual Studio 2010 database projects states that:

您应该通过在ISNULL函数中包装可以包含NULL值的每一列来明确指示如何在比较表达式中处理NULL值.

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.

但是，在以下情况下，违反了代码分析规则 SR0006 . >

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.

这是否也适用于ISNULL，或者ISNULL永远不会导致表扫描?

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)

SR0007 规则是非常糟糕的一揽子建议，因为它使谓词无法确定并且意味着任何列上的索引将无用.即使该列上没有索引，它仍可能使基数估计不准确，从而影响计划的其他部分.

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.

Microsoft.Performance类别中的分类非常有趣，因为它似乎是由不了解查询性能的人编写的.

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.

它声称理由是

如果您的代码将两个NULL值或一个NULL值与任何其他值进行比较 值，您的代码将返回未知结果.

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

虽然表达式本身的确计算为unknown，但是一旦您了解与NULL进行的任何=，<>，>，<等比较，其结果都为，并且WHERE子句仅返回表达式计算结果为true的行.

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.

它们可能意味着 ANSI_NULLS 已关闭，但他们给出了示例无论如何，WHERE ISNULL([c2],0) > 2; vs WHERE [c2] > 2;的文档中的设置都不会受到此设置的影响.此设置

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

仅在比较操作数之一为 要么是NULL变量，要么是文字NULL.

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 

这篇关于在ISNULL中包装可为空的列会导致表扫描吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！