Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Use of N with unicode constant parameter


Use of N with unicode constant parameter

Author
Message
SweetCarrot
SweetCarrot
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 59
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
SrcName
SrcName
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 394
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.
SweetCarrot
SweetCarrot
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 59
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?
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10646 Visits: 11980
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search