Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use of N with unicode constant parameter Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:22 AM
Points: 5, Visits: 52
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
Post #1498475
Posted Wednesday, September 25, 2013 11:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:07 AM
Points: 104, Visits: 335
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.

Post #1498495
Posted Thursday, September 26, 2013 5:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:22 AM
Points: 5, Visits: 52
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?
Post #1498772
Posted Thursday, September 26, 2013 11:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 7,742, Visits: 9,491
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
Post #1498991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse