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.
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不会导致表扫描？
解决方法
SR0007规则是非常糟糕的一般建议,因为它使谓词不可缩放,意味着该列上的任何索引将无用.即使列上没有索引,它仍然可能使基数估计不准确影响计划的其他部分.
它在Microsoft.Performance类别中的分类是相当有趣的,因为它似乎是由没有了解查询性能的人写的.
它声称的理由是
If your code compares two NULL values or a NULL value with any other
value,your code will return an unknown result.
虽然表达式本身确实评估为未知,但您的代码在您明白any =,>,<等于NULL与NULL进行比较,评估为“未知”,并且WHERE子句仅返回表达式求值为true的行. 这可能意味着如果ANSI_NULLS关闭,但它们在WHERE ISNULL([c2],0)>的文档中给出的示例. 2; vs WHERE [c2]> 2;不会受到这种设置的影响.这个设置
affects a comparison only if one of the operands of the comparison is
either a variable that is NULL or a literal NULL.
执行计划显示扫描对寻求或下面
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
