Home Library Learn Downloads Support Community
Sign in | United States - English | |
5 out of 44 rated this helpful Rate this topic

nchar and nvarchar (Transact-SQL)

SQL Server 2008 R2

Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.

nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Use nchar when the sizes of the column data entries are probably going to be similar.

Use nvarchar when the sizes of the column data entries are probably going to vary considerably.

sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.

Objects that use nchar or nvarchar are assigned the default collation of the database unless a specific collation is assigned using the COLLATE clause.

SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.

Did you find this helpful?
(2000 characters remaining)
Community Content Add
Annotations FAQ
term UNICODE UCS-2 is not accurate
5/20/2011 Vlad Nevsky:

> reference:
> http://unicode.org/faq/basic_q.html

> Q: What is the difference between UCS-2 and UTF-16?
> A: UCS-2 is obsolete terminology which refers to a Unicode implementation up to Unicode 1.1, before surrogate code points and UTF-16 were added to Version 2.0 of the standard. This term should now be avoided.


8/2/2011 opc.three:

The above comment is innaccurate. While it is true that UCS-2 is considered "old", UCS-2 is in fact the encoding used within SQL Server. i.e. per the same unicode.org FAQ:

Sometimes in the past an implementation has been labeled "UCS-2" to indicate that it does not support supplementary characters and doesn't interpret pairs of surrogate code points as characters. Such an implementation would not handle processing of character properties, code point boundaries, collation, etc. for supplementary characters. [AF]

The description is relevant to SQL Server's implementation. While surrogate code points are not refused, they are not natively supported. Only the BMP is supported natively, which does exactly overlay UTF-16, but to say that UCS-2 should be avoided in this context is incorrect.
History
Size on row page of nvarchar(n) and nvarchar(max)
A row can only have 8060 bytes of data, per the SQL 2008 R2 Maximum Capacity Specifications.  How much space does an nvarchar(n), where n < 4000 characters, take on the row data page?  Likewise, how much space does an nvarchar(max) take, which should be different since all the data is stored off page?
History
Storage requirement for nvarchar
This is because the length of the data is 6. To store it in the database requires 2 extra bytes apparently, but this does not mean the data itself uses 2 extra bytes.

History