Why use N before any string

Question

Why use N before any string

vikrantkale18

Mr or Mrs. 500

Points: 507
More actions
March 9, 2010 at 10:08 pm

#221413

Why we use N before inserting or comparing any string is there any advantage of it if yes please let me know
as explained in below example :
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Department]') AND type IN (N'U'))
BEGIN
DROP TABLE [Department]
END
CREATE TABLE [Department](
[DepartmentID] [int] NOT NULL PRIMARY KEY,
[Name] VARCHAR(250) NOT NULL,
) ON [PRIMARY]
INSERT [Department] ([DepartmentID], [Name])
VALUES (1, N'Engineering')
INSERT [Department] ([DepartmentID], [Name])
VALUES (2, N'Administration')
INSERT [Department] ([DepartmentID], [Name])
VALUES (3, N'Sales')
INSERT [Department] ([DepartmentID], [Name])
VALUES (4, N'Marketing')
INSERT [Department] ([DepartmentID], [Name])
VALUES (5, N'Finance')
GO
SELECT * FROM [Department]

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The N in front of a string like this, N'A String Constant', is a UNICODE string. You can find more about these in Books Online, the SQL Server Help System.

vidya_pande SSCertifiable Points: 5275 More actions · Answer 2

Yes N' makes the string Unicode.

Text from BOL:

Unicode strings have a format similar to character strings but are preceded by an N identifier (N stands for National Language in the SQL-92 standard). The N prefix must be uppercase. For example, 'Michél' is a character constant while N'Michél' is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not evaluated by using a code page. Unicode constants do have a collation. This collation primarily controls comparisons and case sensitivity. Unicode constants are assigned the default collation of the current database, unless the COLLATE clause is used to specify a collation. Unicode data is stored by using 2 bytes per character instead of 1 byte per character for character data

vikrantkale18 Mr or Mrs. 500 Points: 507 More actions · Answer 3

vikrantkale18

Mr or Mrs. 500

Points: 507

March 10, 2010 at 12:24 am

#1131169

Thanks vidya_pande

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Make sure that you understand the last part of that Books Online entry... it uses 2 BYTES for every character. That's twice the network traffic, twice the memory used, twice the hard disk space used. If you need the global capabilities of Unicode then, by all means, use it. If not and depending on whether there's a chance or not of the column being used in a global environment, you may want to avoid it.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vikrantkale18 Mr or Mrs. 500 Points: 507 More actions · Answer 5

vikrantkale18

Mr or Mrs. 500

Points: 507

March 10, 2010 at 9:31 pm

#1131766

Thanks for guidence , Jeff Moden

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

You're welcome. Thank you for the feedback.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)