Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan
-- 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
Query 3 implicitly converts the VarcharId column to int (which has higher precendence) and this causes the predicate to be non SARGable and thus causes a table scan.
However, when I run a similar test, I did not get the results I expected:
-- 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
All four queries show an index seek (although query two runs the seek through a nested loop with a computer scalar)
Given that NVARCHAR has a higher data type precendence than VARCHAR, I expected to see the VarId column implicitly converted to a VARCHAR and therefore causing a table scan.
Why / how does datatype precendence bahave differently when converting between varchar / nvarchar types?
Best Answer
Well since my post I have managed to find my answer here.
The behaviour of whether a scan or seek will be performed when performing an implicit conversion from VARCHAR to NVARCHAR on the column in the predicate is dependent on the collation setting.
Older, legacy collation settings will cause an index scan and the newer collation settings will cause the seek