Thursday, July 20, 2017

ntext, nchar and nvarchar differences

These are basically used for textual information related data types. There is a strong recommendation not to use ntext as it is not going to be supported.

These are their definitions from Microsoft Documentation.

ntext (National Text):
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered

nchar [ ( n ) ]
Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character..
1 nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

ntext is supposed to be discontinued, but in actuality it is still present as a choice in data types listing even in SQL Server 16.