2 added 5 characters in body
source | link
-- 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) = 10N'10'
SELECT * FROM Ids WHERE NvarId = @id
SELECT * FROM Ids WHERE VarId = @id
GO

DECLARE @id VARCHAR(4) = 10'10'
SELECT * FROM Ids WHERE NvarId = @id
SELECT * FROM Ids WHERE VarId = @id
-- 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) = 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
-- 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
1
source | link

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) = 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?