Sfrgttk

I need to drive a 7/16" nut but am unsure how to use the socket I bought for my screwdriver

Why is stat::st_size 0 for devices but at the same time lseek defines the device size correctly?

Why does Deadpool say "You're welcome, Canada," after shooting Ryan Reynolds in the end credits?

How Did the Space Junk Stay in Orbit in Wall-E?

Ban on all campaign finance?

Why would a flight no longer considered airworthy be redirected like this?

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

2D counterpart of std::array in C++17

How to simplify this time periods definition interface?

Old race car problem/puzzle

My adviser wants to be the first author

Rejected in 4th interview round citing insufficient years of experience

Possible Leak In Concrete

Theorems like the Lovász Local Lemma?

How do anti-virus programs start at Windows boot?

How to generate globally unique ids for different tables of the same database?

Professor being mistaken for a grad student

Have researchers managed to "reverse time"? If so, what does that mean for physics?

Is Mortgage interest accrued after a December payment tax deductible?

At what level can a dragon innately cast its spells?

Check this translation of Amores 1.3.26

Informing my boss about remarks from a nasty colleague

Instead of Universal Basic Income, why not Universal Basic NEEDS?

Is it normal that my co-workers at a fitness company criticize my food choices?

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

Mysql int vs varchar as primary key (InnoDB Storage Engine?SHOWPLAN does not display a warning but “Include Execution Plan” does for the same queryReading a SQL Server Execution planHow to avoid implicit conversion for an Integer columnMySQL not using index on large WHERE IN clauseImplicit conversion does not affect performanceInsert results of spBlitzIndex stored procedure into tablePlan changes to include Eager Spool causes the query to run slowerUNION ALL implicit conversion for VARCHAR(MAX) columnWhy does this SQL Server execution plan contain a snapshot cursor for a select statement?

0

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?

performance sql-server-2014 execution-plan datatypes type-conversion

share

asked 2 mins ago

SEarle1986SEarle1986

477316

add a comment | 

0

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?

performance sql-server-2014 execution-plan datatypes type-conversion

share

asked 2 mins ago

SEarle1986SEarle1986

477316

add a comment | 

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?

performance sql-server-2014 execution-plan datatypes type-conversion

share

asked 2 mins ago

SEarle1986SEarle1986

477316

-- 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

-- 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

share

asked 2 mins ago

SEarle1986SEarle1986

477316

share

asked 2 mins ago

SEarle1986SEarle1986

477316

asked 2 mins ago

SEarle1986SEarle1986

477316

asked 2 mins ago

SEarle1986SEarle1986

477316