diffrence between N'' and cast( as nvarchar)

Question

diffrence between N'' and cast( as nvarchar)

koteswara kishore K.

Ten Centuries

Points: 1122
More actions
October 17, 2008 at 3:33 am

#192249

What is the difference between in below two statements
select N'产品的价格点编号' as china
(Vs)
select cast('产品的价格点编号' as nvarchar(100)) as china
thanks
kishore KK

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398730 More actions · Answer 1

Nothing really. The N is meant for use with string constants, the way you used it. The CAST is meant to work with just about any structural object that you need to convert, parameters, columns or a string constant. Generally, being totally lazy, I use the N for strings.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

koteswara kishore K. Ten Centuries Points: 1122 More actions · Answer 2

But when i execute these two statements

getting irrelavent information in second select(???????)

can you give more or refferences to get analyze my expectations.

Thanks

Kishore KK.

Grant Fritchey SSC Guru Points: 398730 More actions · Answer 3

You need to modify the code page in your query window to appropriately display the data. I'm actually not entirely sure how to do that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398730 More actions · Answer 4

The areas I've been looking at are:

http://msdn.microsoft.com/en-us/library/ms144250(SQL.90).aspx

and

http://msdn.microsoft.com/en-us/library/ms180175(SQL.90).aspx

I'm just not sure how this translates the data on the screen.

I tried this, but it didn't work:

select N'????????' COLLATE Chinese_PRC_BIN as china

select cast('????????' as nvarchar(100)) COLLATE Chinese_PRC_BIN as china

Other than that... I'm stuck. Sorry.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

koteswara kishore K. (10/17/2008)
But when i execute these two statements
getting irrelavent information in second select(???????)
can you give more or refferences to get analyze my expectations.
Thanks
Kishore KK.

This is where the dragon start chasing its tail. Not putting the N in front of the string inside of the CAST makes SQL Server treat the string as NON-unicode (or as a VarChar). The Unicode character (2bytes) then get seen as 2 characters (the 2 halves), both of which are apparently non-printing or non-valid, thus the question marks.

You'd STILL have to include the N' in the statement, as in:

select cast(N'????????' as nvarchar(100)) as china

In which case the CAST just allows you to set the max length.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Grant Fritchey SSC Guru Points: 398730 More actions · Answer 6

Oops. Missed that entirely. Thanks Matt.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning