对带有空白 NVARCHAR 或 NULL 检查的 VARCHAR 索引进行 Count(*) 会导致返回的行数加倍

我有一张桌子，上面有VARCHAR列及其上的索引。每当一个SELECT COUNT(*)是在这张表上完成的，该表检查了COLUMN = N'' OR COLUMN IS NULL它返回双倍的行数。SELECT *与相同的where子句将返回正确的记录数。

读完这篇文章后：https://sqlquantumleap.com/2017/07/10/impact-on-indexes-when-mixing-varchar-and-nvarchar-types/ https://sqlquantumleap.com/2017/07/10/impact-on-indexes-when-mixing-varchar-and-nvarchar-types/并进行一些测试，我相信列的排序规则和隐式转换不是错误（至少不是直接错误）。该列的排序规则是Latin1_General_CI_AS.

数据库在SQL Server 2012上，我也在2016上测试过。

我创建了一个测试脚本（如下）来演示这个问题。这样做，我相信它可能与数据分页有关，因为它需要表中的一些数据才能发生。

CREATE TABLE [dbo].TEMP 
(
    ID [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [DATA] [varchar](200) COLLATE Latin1_General_CI_AS NULL,
    [TESTCOLUMN] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_TEMP] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO

CREATE NONCLUSTERED INDEX [I_TEMP_TESTCOLUMN] ON dbo.TEMP (TESTCOLUMN ASC)
GO

DECLARE @ROWS AS INT = 40; 

WITH NUMBERS (NUM) AS 
(
    SELECT 1 AS NUM
    UNION ALL
    SELECT NUM + 1 FROM NUMBERS WHERE NUM < @ROWS
)
INSERT INTO TEMP (ID, DATA)
SELECT NUM, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901324561234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' 
FROM NUMBERS

SELECT @ROWS AS EXPECTED, COUNT(*) AS ACTUALROWS
FROM TEMP
GO

SELECT COUNT(*) AS INVALIDINDEXSEARCHCOUNT
FROM TEMP
WHERE (TESTCOLUMN = N'' OR TESTCOLUMN IS NULL)
GO

DROP TABLE TEMP

我能够在某种程度上修改数据库（我无法更改数据，或更改允许的列NULL），不幸的是我无法修改进行搜索的代码，任何人都可以找到一种方法来获得正确的结果COUNT(*)结果返回？

TLDR:这是产品中的一个错误（已报告here https://feedback.azure.com/d365community/idea/d12ba420-6d74-ed11-a81b-000d3ae49307).

暴露此错误的不良做法是数据类型不匹配（varchar列被比较nvarchar) - 在 SQL 排序规则上，这只会导致列隐式转换为nvarchar和全面扫描。

在 Windows 排序规则上，这仍然会导致查找。这通常是一个有用的性能优化，但在这里你遇到了一个边缘情况......

更多详情：使用以下设置...

CREATE TABLE dbo.TEMP 
(
    ID INT IDENTITY PRIMARY KEY,
    [TESTCOLUMN] [varchar](50) COLLATE Latin1_General_CI_AS NULL INDEX [I_TEMP_TESTCOLUMN],
    Filler AS CAST('X' AS CHAR(8000)) PERSISTED
)

--Add 7 rows where TESTCOLUMN is NOT NULL
INSERT dbo.TEMP([TESTCOLUMN]) VALUES ('aardvark'), ('badger'), 
                                     ('badges'), ('cat'), 
                                     ('dog'), ('elephant'), 
                                     ('zebra');

--Add 49 rows where TESTCOLUMN is NULL 
INSERT dbo.TEMP([TESTCOLUMN]) 
SELECT NULL 
FROM dbo.TEMP T1 CROSS JOIN dbo.TEMP T2

那么首先看一下实际的执行计划

SELECT COUNT(*) 
FROM dbo.TEMP
WHERE TESTCOLUMN = N'badger'
OPTION (RECOMPILE)

在 SQL 排序规则中，隐式转换为nvarchar将使谓词完全不可控制。通过 Windows 排序规则，SQL Server 能够将设备添加到计算标量调用内部函数的计划中GetRangeThroughConvert(N'badger',N'badger',(62))结果值最终被输入到嵌套循环连接中，以给出索引查找的起点和终点。 （文章《动态查找和隐藏的隐式转换 https://www.sql.kiwi/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html“有一些关于这个计划形状的更多细节）

执行计划中没有公开此内部函数返回的范围起始值和结束值，但如果您碰巧有一个可用的 SQL Server 版本（其中短暂存在），则可以看到它们query_trace_column_values https://www.sqlshack.com/query-trace-column-values/扩展事件尚未被禁用。在上面的例子中，函数返回(badger, badgeS, 62)这些值用于索引查找。在本例中，当我添加了一行值为“badges”的行时，查找最终会比严格需要的行多读取一行，并且剩余谓词仅保留“badger”的行。

Now try

SELECT COUNT(*) 
FROM dbo.TEMP
WHERE TESTCOLUMN = N''
OPTION (RECOMPILE)

The GetRangeThroughConvert当要求提供空字符串和输出的范围时，函数似乎放弃(null, null, 0).

The null这里表示范围的末尾是无界的，因此有效地索引查找最终会读取从第一行到最后一行的整个索引。

上面显示索引查找读取了所有 56 行，但剩余谓词完成了删除所有不匹配行的工作TESTCOLUMN = N''（因此运算符返回零行）。

一般来说，这里使用的搜索谓词看起来就像一个前缀搜索（例如，seek[TESTCOLUMN] = N'A'将至少读取以以下内容开头的所有行A剩余谓词进行相等性检查），所以我对这里的空字符串的期望首先不会很高，但是保罗·怀特表示 https://www.sql.kiwi/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html?showComment=1670328317977#c5212039192927765430无论如何，这里正在寻找的范围可能是一个错误。

当您添加OR执行计划更改的查询的谓词。

现在，它最终将两个外部行连接到嵌套循环连接，因此最终执行两次搜索（在嵌套循环内部执行两次搜索运算符）。

一个为TESTCOLUMN = N''案例和一个TESTCOLUMN IS NULL案件。用于的值TESTCOLUMN = N''分支仍然通过计算GetRangeThroughConvert调用（因为这是 SQL Server 可以查找这种不匹配数据类型情况的唯一方法），因此仍然具有扩展范围，包括NULL.

问题是索引查找上的剩余谓词现在也发生了变化。

就是现在

CONVERT_IMPLICIT(nvarchar(50),[tempdb].[dbo].[TEMP].[TESTCOLUMN],0)=N'' 
OR [tempdb].[dbo].[TEMP].[TESTCOLUMN] IS NULL 

之前的残差谓词

CONVERT_IMPLICIT(nvarchar(50),[tempdb].[dbo].[TEMP].[TESTCOLUMN],0)=N''

不合适，因为这会错误地删除带有NULL需要保留的OR TESTCOLUMN IS NULL branch.

这意味着当寻求N''分支完成后它仍然会读取所有行NULL和以前一样，但剩余谓词不再适合删除这些谓词。

问题计划中的合并间隔没有合并索引查找的重叠范围，这似乎也有点失误。

我认为由于两个分支的标志值不同，这种情况不会发生。Expr1014 has a 的价值60 https://dba.stackexchange.com/a/14812/3690为了IS NULL分支和0为了= N'' branch.