Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan

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

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

StackExchange Database Administrators Q#232196, answer score: 5