September 25, 2013 at 10:18 am
I am trying to get my arms around needed code changes - as per a database conversion from varchar to nvarchar.
Using SQL Server 2012
Database Collation: SQL_Latin1_General_CP1_CI_AS
Assuming an indexed field is defined nvarchar.
If limiting by that field using a constant, when is N needed/best used?
i.e. Select * from AUTest where Code='ABC' vs Select * from AUTest where Code=N'ABC'
- when I look at the execution plan, an index seek is used whether N used or not.
- N is needed in example: Select * from AUTest where Code=N'Pūblius Cornēlius Scīpiō Africānus'
- But this works fine: Select * from AUTest where Code='Björk Guðmundsdóttir Häkkinen'
Is the N needed only if the specific character string contains non ASCII characters?
-------------------------------------------
TSQL
CREATE TABLE AUTest(
AUTestID int IDENTITY(1,1) NOT NULL,
Code nvarchar(100) NOT NULL,
CONSTRAINT PK_AUTest PRIMARY KEY CLUSTERED (AUTestID ASC)
)
CREATE NONCLUSTERED INDEX IX_AUTest ON AUTest (Code ASC)
-----------------------
insert into autest(Code) select 'Björk Guðmundsdóttir Häkkinen'
insert into autest(Code) select 'Pūblius Cornēlius Scīpiō Africānus'
insert into autest(Code) select '솅조ᇰ (세종대왕)'
insert into autest(Code) select N'Björk Guðmundsdóttir Häkkinen'
insert into autest(Code) select N'Pūblius Cornēlius Scīpiō Africānus'
insert into autest(Code) select N'솅조ᇰ (세종대왕)'
select * from autest order by AUTestID
September 25, 2013 at 11:01 am
N denotes that the subsequent string is in Unicode, means National language character set.
When you use unicode you mast have Nvarchar/Nchar ...
Index will be used anyway, but there are some performance issues in case when
for example
TABLE1 you have created index on columnA which is varchar type,
TABLE2 you have created index on columnB which is Nvarchar type
join this two tables
select * from TABLE1 a join TABLE2 b on a.coumnA = b.columnB
view execution plan
after this, second case
alter TABLE1 alter column columnA Nvarchar
do the same select, view execution plan
you will see deference.
September 26, 2013 at 5:29 am
Thank you for the reply - I sure like SQL Central - lots of good info.
It is good for us all to state and understand that join fields need to be of same type.
However, my central question - very specifically - when/why is the N needed.
Specifically in a database upgrade from varchar to nvarchar.
If 250 out of 2500 SPs contain constant value parameters being set in queries...
Do all those SPs and statements now need change - with an N in front of the constant?
I hypothesize that the N ONLY MATTERS when the constant's value is outside the database collation.
I am not changing the collation.
I do not think all those SP lines need changing.
Thoughts?
September 26, 2013 at 11:58 am
No, the correlation is irrelevant.
You will get a performance diference between using N to make a string literal NCHAR and not using it so that it is CHAR. If you are comparing with a variable or an indexed column, the performance difference will be so small you won't notice it and probably won't be able to measure it because the number of conversions required is very small; on the other hand, there may be times when the need for conversion makes a big difference, so it's always safest to make sure that your literal is NCHAR if the column you are associating it with is NCHAR or NVARCHAR and not if it isn't, to avoid performance problems.
Tom
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply