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

N' annotation Expand / Collapse
Author
Message
Posted Wednesday, July 31, 2013 6:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:33 AM
Points: 8, Visits: 75
I am still trying to make sense of the whole n' when selecting text or varchar values... Is that so necessary? Can someone give me an example?
Post #1479759
Posted Wednesday, July 31, 2013 7:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 3,648, Visits: 5,322
For display purposes these 2 results would be identical (except of course for the specific words):

SELECT 'THIS IS A VARCHAR STRING', N'THIS IS AN NVARCHAR STRING';


There may be a slight performance boost in a case where you do an assignment to a type VARCHAR variable or column, like:

DECLARE @MyString NVARCHAR(100);

SELECT @MyString = N'THIS IS AN NVARCHAR STRING';


Because if you omit the N' annotation, SQL would need to do an implicit conversion to the NVARCHAR type. So the annotation simply defines the string that follows as Unicode string.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1479766
Posted Wednesday, July 31, 2013 8:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
If you ever use dynamic SQL using the built-in system function 'sp_executeSQL' and try to use an OUTPUT parameter the function will not work unless you explicitly append the N' prefix.

sp_executesql (Transact-SQL)


 
Post #1479772
Posted Wednesday, July 31, 2013 9:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 37,080, Visits: 31,640
grecci1077 (7/31/2013)
I am still trying to make sense of the whole n' when selecting text or varchar values... Is that so necessary? Can someone give me an example?


To summarize what the other folks on this thread have said, the N' is how you mark string literals as NVARCHAR literals. If you're working with NVARCHAR datatypes, such as when they appear in sp_ExecuteSQL or table columns with an NVARCHAR datatype, it's best to use the N' prefix. It's also best NOT to use it on string literals when comparing to a VARCHAR column in a table because using the N' prefix on a literal to be compared to a VARCHAR column requires that the whole column of data must be converted before the comparison can be made. That's obviously a "Non SARGable" comparison of the worst kind.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1479776
Posted Thursday, August 1, 2013 3:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 3,648, Visits: 5,322
Steven Willis (7/31/2013)
If you ever use dynamic SQL using the built-in system function 'sp_executeSQL' and try to use an OUTPUT parameter the function will not work unless you explicitly append the N' prefix.

sp_executesql (Transact-SQL)


 


Steven - That is a very good point. I've seen several cases where sp_executesql is sensitive to passing in only N' strings.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1479850
Posted Thursday, August 1, 2013 7:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:57 AM
Points: 802, Visits: 717
As others have said, the presence/absence of the N determines the data of the literal. Try this:

SELECT 'άλφα', N'άλφα'

As Jeff pointed out, you should not use nvarchar just to be safe, but pick the data type that fits your context. Else you can cause performance disasters.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1479942
Posted Wednesday, September 25, 2013 11:55 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:35 AM
Points: 109, Visits: 261
I've noticed when you have code like:

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '... very long sql' + CAST(@someparam AS VARCHAR) + 'continuation of long sql' + '...'


And your @SQL is nearing 4k character limit, you need to explicitely convert all the strings into NVARCHAR(MAX) and use + N'continuation of sql...' strings
Post #1498521
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse