April 14, 2015 at 2:29 am
Hi All,
I just wondered if anyone could tell me how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.S
To summarise, in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
Many thanks.
Thom
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 14, 2015 at 3:31 am
I modified you code a little adding index to table.
CREATE Table T (S varchar(2))
Declare @S nvarchar(255)
CREATE INDEX ts ON T (S);
Insert into T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.S
DROP TABLE T;
Execution plan shows that there only implicit conversion is done to literals in case for example 'Auto Repairs' is converter to N'Auto Repairs'. SQL Server is able to use index for seeking from T.
April 14, 2015 at 3:44 am
That's great, thanks.
Having a further fiddle with your code, showed me that the literal string after the then is always returned as a varchar, regardless as to what the variable and table field are declared as. The literal string after the when, however, is treated as whatever the variable is, which is interesting. Seems that the server is attempting tobe intelligent on one string, but not the other.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 14, 2015 at 4:29 am
You can force the literal string to be NVARCHAR by using the N'Somestring' syntax. Try that with the same code.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply