Post reply

Why use N before any string

  • 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]

  • March 9, 2010 at 11:40 pm

    #1131153

    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.

  • March 10, 2010 at 12:05 am

    #1131160

    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

  • March 10, 2010 at 12:24 am

    #1131169

    Thanks vidya_pande

  • March 10, 2010 at 5:53 pm

    #1131732

    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)
    Intro to Tally Tables and Functions

  • March 10, 2010 at 9:31 pm

    #1131766

    Thanks for guidence , Jeff Moden

  • March 10, 2010 at 9:43 pm

    #1131770

    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)
    Intro to Tally Tables and Functions

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

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