When should I use the SQL Server Unicode 'N' Constant?

Question

I've been looking into the use of the Unicode 'N' constant within my code, for example:

select object_id(N'VW_TABLE_UPDATE_DATA', N'V');

insert into SOME_TABLE (Field1, Field2) values (N'A', N'B');

After doing some reading around when to use it, and I'm still not entirely clear as to the circumstances under which it should and should not be used.

Is it as simple as using it when data types or parameters expect a unicode data type (as per the above examples), or is it more sophiticated than that?

The following Microsoft site gives an explanation, but I'm also a little unclear as to some of the terms it is using http://msdn.microsoft.com/en-us/library/ms179899.aspx

Or to precis:

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.

What does it mean by:

• 'evaluated by using a code page'?
• Collation?

I realise this is quite a broad question, but any links or help would be appreciated.

Thanks

Answer 1

Is it as simple as using it when data types or parameters expect a unicode data type?

Pretty much.

To answer your other points:

A code page is another name for encoding of a character set. For example, windows code page 1255 encodes Hebrew. This is normally used for 8bit encodings for characters. In terms of your question, strings may be evaluated using different code pages (so the same bit pattern may be interpreted as a Japanese character or an Arabic one, depending on what code page was used to evaluate it).

Collation is about how SQL Server is to order strings - this depends on code page, as you would order strings in different languages differently. See this article for an example.

Answer 2

National character nchar() and nvarchar() use two bytes per character and support international character set -- think internet. The N prefix converts a string constant to two bytes per character. So if you have people from different countries and would like their names properly stored -- something like:

CREATE TABLE SomeTable ( 
   id int
  ,FirstName nvarchar(50)
  );

Then use:

INSERT  INTO SomeTable
        ( Id, FirstName )
VALUES  ( 1, N'Guðjón' );

and

SELECT  *
FROM    SomeTable
WHERE   FirstName = N'Guðjón';