January 18, 2013 at 12:50 pm
Why would these two queries produce different execution plan? I am not why would execution plan be different when you the values are appended with N'.
select a,b,c from MyTable
where a in( 'Ab','BC',CD')
select a,b,c from MyTable
where a in(N'Ab',N'BC',N'CD')
These are just examples, but currently i do have actual queries which produce totally different exec plans when the values have N'?
January 18, 2013 at 1:21 pm
2 word: Implicit conversion
SQL Server needs to do implicit conversions that may affect the query performance.
January 18, 2013 at 1:26 pm
Luis Cazares (1/18/2013)
2 word: Implicit conversionSQL Server needs to do implicit conversions that may affect the query performance.
implicit conversion on what?
January 18, 2013 at 1:31 pm
sqldba_newbie (1/18/2013)
Luis Cazares (1/18/2013)
2 word: Implicit conversionSQL Server needs to do implicit conversions that may affect the query performance.
implicit conversion on what?
The N forces conversion to Unicode.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 18, 2013 at 1:38 pm
Using your examples:
( 'Ab','BC',CD') are non-Unicode strings (char/varchar)
(N'Ab',N'BC',N'CD') are Unicode strings (nchar/nvarchar)
The datatypes are differents and the clearest example of it is that a char(1) uses 1byte and nchar(1) uses 2bytes of storage.
There are several differences in the way SQL Server handles each of them and each scenario will define which is the best option.
If your column a is defined as a Unicode string, then the first example will have an implicit conversion or viceversa.
Even different lengths might cause it.
An implicit conversion might affect the use of indexes as well. You might want to check more on this issue but I don't have much time to give you complete references to this.
January 18, 2013 at 1:45 pm
Luis Cazares (1/18/2013)
Using your examples:( 'Ab','BC',CD') are non-Unicode strings (char/varchar)
(N'Ab',N'BC',N'CD') are Unicode strings (nchar/nvarchar)
The datatypes are differents and the clearest example of it is that a char(1) uses 1byte and nchar(1) uses 2bytes of storage.
There are several differences in the way SQL Server handles each of them and each scenario will define which is the best option.
If your column a is defined as a Unicode string, then the first example will have an implicit conversion or viceversa.
Even different lengths might cause it.
An implicit conversion might affect the use of indexes as well. You might want to check more on this issue but I don't have much time to give you complete references to this.
This helps. Thanks.
January 18, 2013 at 3:09 pm
Because the second query becomes essentially
select a,b,c from MyTable
where CAST(a AS nvarchar(2)) in(N'Ab',N'BC',N'CD')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply