Performance
VARCHAR 列到 NVARCHAR 的隱式轉換不會導致預期的表掃描
給定以下腳本,我可以看到隱式轉換和數據類型優先級對查詢計劃有負面影響
-- create objects CREATE DATABASE ConvertTest GO USE ConvertTest GO CREATE TABLE Person ( VarcharId NVARCHAR(4), IntId INT ) -- insert data INSERT INTO Person SELECT TOP 1000 CONVERT(NVARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id)), ROW_NUMBER() OVER (ORDER BY a.object_id) FROM sys.objects a CROSS JOIN sys.objects b -- create indexes CREATE INDEX IX_Varchar ON Person ( VarcharId, IntId ) CREATE INDEX IX_Int ON Person ( IntId, VarcharId ) DECLARE @id NVARCHAR(4) = 100 -- statement 1 SELECT * FROM Person WHERE VarcharId = @id -- index seek -- statement 2 SELECT * FROM Person WHERE IntId = @id -- index seek GO DECLARE @id INT = 100 -- statement 3 SELECT * FROM Person WHERE VarcharId = @id -- index scan -- statement 4 SELECT * FROM Person WHERE IntId = @id -- index seek
查詢 3 將 VarcharId 列隱式轉換為 int(優先級更高),這會導致謂詞不可 SARGable,從而導致表掃描。
但是,當我執行類似的測試時,並沒有得到預期的結果:
-- create objects CREATE DATABASE ConvertTest2 GO USE ConvertTest2 GO CREATE TABLE Ids ( NvarId NVARCHAR(4), VarId VARCHAR(4) ) -- insert data INSERT INTO Ids SELECT TOP 1000 CONVERT(NVARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id)), CONVERT(VARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id)) FROM sys.objects a CROSS JOIN sys.objects b -- create indexes CREATE INDEX IX_NvarId ON Ids ( NvarId, VarId ) CREATE INDEX IX_VarId ON Ids ( VarId, NvarId ) DECLARE @id NVARCHAR(4) = N'10' SELECT * FROM Ids WHERE NvarId = @id SELECT * FROM Ids WHERE VarId = @id GO DECLARE @id VARCHAR(4) = '10' SELECT * FROM Ids WHERE NvarId = @id SELECT * FROM Ids WHERE VarId = @id
所有四個查詢都顯示了索引查找(儘管查詢二通過帶有電腦標量的嵌套循環執行查找)
鑑於 NVARCHAR 具有比 VARCHAR 更高的數據類型優先級,我希望看到 VarId 列隱式轉換為 VARCHAR 並因此導致表掃描。
為什麼/如何在 varchar / nvarchar 類型之間轉換時數據類型的優先級會有所不同?
好吧,自從我的文章以來,我設法在這裡找到了答案。
在謂詞中的列上執行從 VARCHAR 到 NVARCHAR 的隱式轉換時是否執行掃描或查找的行為取決於排序規則設置。
較舊的舊排序規則設置將導致索引掃描,而較新的排序規則設置將導致查找