Index Seek 根據參數值掃描整個表

我有一個查詢：

SELECT  Id, 
       ColumnA,
       ColumnB
FROM    MyTable
WHERE   ColumnA = @varA OR
       ColumnB = @varB  

該表定義為

CREATE TABLE MyTable
(
   Id INT IDENTITY(-2147483648,1) PRIMARY KEY,
   ColumnA VARCHAR(22)
   ColumnB VARCAHR(22)
)

並且表上有一個非聚集索引

CREATE INDEX IX_MyIndex ON MyTable
(
   ColumnA
)

當我使用以下參數執行查詢時：

DECLARE @varA nvarchar(4000) = ''
DECLARE @varB  nvarchar(8) = '10140730'

執行計劃顯示了一個索引搜尋IX_MyIndex，但是它顯示讀取的行數為 1700 萬行，但實際行數為 0（MyTable.ColumnA 中有 0 行，值為 ‘’）如果我打開SET STATISTICS IO ON，我可以看到完整的表正在閱讀

這是有道理的：這篇文章在“這是一個“壞”的索引搜尋”部分

但是，當我使用參數執行相同的查詢時：

DECLARE @varA nvarchar(8) = 'a'
DECLARE @varB  nvarchar(8) = '10140730'

seek 運算符沒有“讀取的行數”屬性（MyTable.ColumnA 中有 0 行，值為 ‘a’）並SET STATISTICS IO報告單個數字邏輯讀取

順便說一句，該計劃有一個隱式轉換警告，當我像這樣更改查詢時問題就消失了：

SELECT  Id, 
       ColumnA,
       ColumnB
FROM    MyTable
WHERE   ColumnA = CONVERT(VARCHAR(22),@varA) OR
       ColumnB = CONVERT(VARCHAR(22),@varB)

或將基礎列更改為NVARCHAR

但是，我很好奇為什麼具有兩個不同值的索引搜尋的行為@varA是不同的，即使它們都返回表中相同數量的記錄（0）

當列和變數的數據類型不匹配時，SQL Server 不能直接使用 B 樹索引的查找能力來定位正確的值範圍。

當數據類型優先規則意味著必須將列數據轉換為變數的數據類型時，這將意味著掃描整個表或索引，轉換每個值並作為殘差謂詞針對變數進行測試。

這顯然不是理想的，但很常見（不幸的是）SQL Server 在這些情況下有一個內置的方法來實現索引查找。它採用提供的值併計算它映射到的值的範圍，考慮類型轉換和排序規則。

此功能稱為動態搜尋，計算映射範圍的內部方法稱為GetRangeThroughConvert。

例如，當nvarchar變數包含“a”時，數據類型的映射值範圍varchar可能是“a”到“B”（確切範圍取決於排序規則）。這意味著 SQL Server 可以查找varchar‘a’ 和 ‘B’ 之間的索引，僅測試以 ‘a’ (as nvarchar) 作為殘差謂詞的匹配項是否相等。

當提供的值為空字元串時，計算範圍是無限的，因此有效地掃描了整個索引。

例如：

DROP TABLE IF EXISTS dbo.MyTable;
GO
CREATE TABLE dbo.MyTable
(
   ColumnA varchar(22) COLLATE Latin1_General_CI_AS NOT NULL
);
GO
INSERT dbo.MyTable 
   WITH (TABLOCKX)
   (ColumnA)
SELECT TOP (1000)
   REPLICATE(CHAR(65 + ROW_NUMBER() OVER (ORDER BY @@SPID) % 26), 22)
FROM master.sys.all_columns AS AC1
CROSS JOIN master.sys.all_columns AS AC2;
GO
CREATE INDEX IX_MyIndex_A ON dbo.MyTable(ColumnA);

以下查詢使用範圍為 ‘a’ 到 ‘B’ 和殘差謂詞的動態搜尋CONVERT_IMPLICIT(nvarchar(22),[dbo].[MyTable].[ColumnA],0)=[@varA]：

DECLARE @varA nvarchar(22) = N'a';

SELECT MT.ColumnA 
FROM dbo.MyTable AS MT 
WHERE MT.ColumnA = @varA;

執行計劃顯示了動態搜尋形狀，其中 38 行被搜尋限定，但最終都被殘差拒絕：

38 行是查詢計算的：

SELECT COUNT_BIG(*)
FROM dbo.MyTable AS MT 
WHERE MT.ColumnA > 'a' 
AND MT.ColumnA < 'B';

當變數包含空字元串時，計算範圍是無限的，因此 seek 有效地掃描整個索引：

DECLARE @varA nvarchar(22) = N''; -- empty string

SELECT MT.ColumnA 
FROM dbo.MyTable AS MT 
WHERE MT.ColumnA = @varA;

執行計劃顯示從索引中讀取的所有 1000 行（但同樣被殘差丟棄）：

空字元串是一種特殊情況，GetRangeThroughConvert不能產生有用的範圍。單個空格字元確實會產生狹窄的搜尋範圍（計劃）。

無論如何，消息是要仔細注意數據類型。

db<>小提琴

引用自：https://dba.stackexchange.com/questions/241112