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?

Solution 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.

Solution 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)

Solution 3

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

Solution 4

The n prefix simply means Unicode. They "n" types work similarly to the plain versions except they work with Unicode text.

char is a fixed length field. Thus char(10) filled with "Yes" will still take 10 bytes of storage.

varchar is a variable length field. char(10) filled with "Yes" will take 5 bytes of storage (there is a 2 byte overhead for using var data types).

char(n) holding string of length x. Storage = n bytes. varchar(n) holding string of length x. Storage = x+2 bytes.

vchar and nvarchar are similar except it is 2 bytes per character.

Generally speaking you should only use char & char (over varchar & nvarchar) when working with fixed or semi-fixed strings. A good example would be a product_code or user_type which is always n characters long.

You shouldn't use text (or ntext) as it has been deprecated. varchar(max) & nvarchar(max) provides the same functionality.

Solution 5

N prefix indicates unicode support and takes up twice the bytes per character of non-unicode.

Varchar is variable length. You use an extra 2 bytes per field to store the length.

Char is fixed length. If you know how long your data will be, use char as you will save bytes!

Text is mostly deprecated in my experience.

Be wary of using Varchar(max) and NVarchar(max) as these fields cannot be indexed.

View more solutions

Comments

[Stu]

msdn.microsoft.com/en-us/library/aa258271(SQL.80).aspx

[TomTom]

-1 from me. space is NOT allocated in blocks of 16 bytes. It takes 16 bytes in the column storage, but it is allocated differently. The rest is ok.

[Gerald Davis]

There is no good case for using text or ntext. It has been deprecated and support will be removed from future versions of SQL server. varchar(max) & nvarchar(max) should be used instead.

[Panzercrisis]

Does varchar still resize to the same extent across all rows for the column?

[juFo]

regarding the deprecated ntext and text: docs.microsoft.com/en-us/sql/t-sql/data-types/… IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.