Difference between different string types in SQL Server?

Question

What is the difference between char, nchar, ntext, nvarchar, text and varchar in SQL?

Is there really an application case for each of these types, or are some of them just deprecated?

Answer 1

text and ntext are deprecated, so lets omit them for a moment. For what is left, there are 3 dimensions:

• Unicode (UCS-2) vs. non-unicode: N in front of the name denotes Unicode
• Fixed length vs. variable length: var denotes variable, otherwise fixed
• In-row vs. BLOB: (max) as length denotes a BLOB, otherwise is an in-row value

So with this, you can read any type's meaning:

• CHAR(10): is an in-row fixed length non-Unicode of size 10
• NVARCHAR(256): is an in-row variable length Unicode of size up-to 256
• VARCHAR(MAX): is a BLOB variable length non-Unicode

The deprecated types text and ntext correspond to the new types varchar(max) and nvarchar(max) respectively.

When you go to details, the meaning of in-row vs. BLOB blurs for small lengths as the engine may optimize the storage and pull a BLOB in-row or push an in-row value into the 'small BLOB' allocation unit, but this is just an implementation detail. See Table and Index Organization.

From a programming point of view, all types: CHAR, VARCHAR, NCHAR, NVARCHAR, VARCHAR(MAX) and NVARCHAR(MAX), support an uniform string API: String Functions. The old, deprecated, types TEXT and NTEXT do not support this API, they have a separate, deperated, TEXT API to manipulate. You should not use the deprecated types.

BLOB types support efficient in-place updates by using the UPDATE table SET column.WRITE(@value, @offset) syntax.

The difference between fixed-length and variable length types vanishes when row-compression on a table. With row-compression enabled, fixed lenght types and variable length are stored in the same format and trailing spaces are not stored on disk, see Row Compression Implementation. Note that page-compression implies row-compression.

Answer 2

• 'n' represents support for unicode characters.
• char - specifies string with fixed length storage. Space allocated with or without data present.
• varchar - Varying length storage. Space is allocated as much as length of data in column.
• text - To store huge data. The space allocated is 16 bytes for column storage.

Additionally - text and ntext have been deprecated for varchar(max) and nvarchar(max)

Answer 3

text and ntext are deprecated in favor of varchar(max) and nvarchar(max)