Why people use "N" before assigning some value ?

Question

Post reply

Why people use "N" before assigning some value ?

mamidimahesh-127681

SSC Eights!

Points: 868
More actions
May 9, 2004 at 11:32 pm

#87144

Hi Friends,
I have a doubt. When I was going through system stored procedures. I found "N" with some given value .
Like
select @data_file_size_str = N'512KB',
select @deflanguage = N'us_english'
Why people use "N" ? What is the meaning ?
Bobby

Viewing 10 posts - 1 through 9 (of 9 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

N' indicates Unicode.

People use it for international application with the needs to store *exotic* characters.

To be honest, I think most people use it because they never checked what the Access Upsizing wizard did.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mamidimahesh-127681 SSC Eights! Points: 868 More actions · Answer 2

Hi Frank,

I did not understand

*exotic* characters ? and Access Upsizing wizard ?

Please explain in detail...

Bobby

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Okay, the remark about the Upsizing Wizard was more or less a joke.

IIRC the wizard converts all Access text fields to NVARCHAR columns.

As for exotic characters:

You need to use datatypes that are unicode-able (like NCHAR or NVARCHAR) when you have the need to store for example arabic, kyrillic, chinese, japanese....data.

If you are interested in this, BOL has many topics on unicode.

Sounds good? Well, the ability to store virtually any character, comes at a certain price.

DECLARE @String1 NCHAR(15)

DECLARE @String2 CHAR(15)

SET @String1 = 'Warum Unicode?'

SET @String2 = @String1

SELECT DATALENGTH(@String1) AS DoppelterPlatzverbrauch, DATALENGTH(@String2) AS EinfacherPlatzverbrauch

DoppelterPlatzverbrauch EinfacherPlatzverbrauch

----------------------- -----------------------

30 15

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Sorry, couldn't get the formatting correct. Try it out and you will see what I mean

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mamidimahesh-127681 SSC Eights! Points: 868 More actions · Answer 5

Hi Frank,

Thanks for giving examples . But where i should use "N" ? In select/insert/update statements ?

Bobby

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Yup, exactly there.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 7

But only if you are imputing a string containing unicode characters or in some cases trying to insert into a n(dataype) field or variable.

From SQL BOL

Unicode Data

Traditional non-Unicode data types in Microsoft® SQL Server™ 2000 allow the use of characters that are defined by a particular character set. A character set is chosen during SQL Server Setup and cannot be changed. Using Unicode data types, a column can store any character defined by the Unicode Standard, which includes all of the characters defined in the various character sets. Unicode data types take twice as much storage space as non-Unicode data types.

Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server. Use these data types for columns that store characters from more than one character set. Use nvarchar when a column's entries vary in the number of Unicode characters (up to 4,000) they contain. Use nchar when every entry for a column has the same fixed length (up to 4,000 Unicode characters). Use ntext when any entry for a column is longer than 4,000 Unicode characters.

Note The SQL Server Unicode data types are based on the National Character data types in the SQL-92 standard. SQL-92 uses the prefix character n to identify these data types and values.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

Some vendors generate ddl which only contains NVARCHAR alphanumeric columns.

Why ? "Just because we do."

IMO this way the used components can allways use the N'..' to address character columns.

SQLServer impact aparently is not their concern. And "yes" these columns are used as PK or index-columns.

Use the N'..' to avoid implicit conversion.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Alamir SSCertifiable Points: 6085 More actions · Answer 9

I think if you use a programming language (or access) and link the form to the Table fields ... then you don't have to write N' ...

Alamir Mohamed
Alamir_mohamed@yahoo.com