SQL Server中不同字符串类型的区别?

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

SQL中的char、nchar、ntext、nvarchar、text和varchar有什么区别?

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

对于这些类型，是否真的有一个应用程序用例，或者它们中的一些只是被弃用了?

9 个解决方案

#1

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

文本和ntext被弃用，因此我们暂时省略它们。剩下的有三个维度:

Unicode (UCS-2) vs. non-unicode: N in front of the name denotes Unicode
Unicode (UCS-2)和非Unicode:名称前面的N表示Unicode
Fixed length vs. variable length: var denotes variable, otherwise fixed
固定长度与可变长度:var表示变量，否则为固定
In-row vs. BLOB: (max) as length denotes a BLOB, otherwise is an in-row value
行内vs BLOB: (max)作为长度表示BLOB，否则为行内值

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

有了这个，你可以读懂任何类型的意思:

CHAR(10): is an in-row fixed length non-Unicode of size 10
CHAR(10):是一个行内固定的长度，没有大小为10的unicode
NVARCHAR(256): is an in-row variable length Unicode of size up-to 256
NVARCHAR(256):是一个行内可变长度的大小为256的Unicode
VARCHAR(MAX): is a BLOB variable length non-Unicode
VARCHAR(MAX):是一个BLOB可变长度的非unicode。

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

不赞成使用的类型text和ntext分别对应于新的类型varchar(max)和nvarchar(max)。

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.

当您详细讨论时，当引擎优化存储并将一个BLOB放入行中或将一个in行值推入“小BLOB”分配单元时，in-row和BLOB的含义会变得模糊，但这只是实现细节。参见表和索引组织。

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.

从编程的角度来看，所有类型:CHAR、VARCHAR、NCHAR、NVARCHAR、VARCHAR(MAX)和NVARCHAR(MAX)，都支持统一的字符串API: string函数。旧的、已弃用的、类型为TEXT和NTEXT不支持这个API，它们有一个单独的、分隔的、要操作的文本API。不应该使用不赞成的类型。

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

BLOB类型通过使用UPDATE table SET列支持高效的就地更新。写(@ value,@offset)语法。

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.

固定长度和可变长度类型之间的区别在表上的行压缩时消失。在启用行压缩的情况下，固定的lenght类型和可变长度以相同的格式存储，并没有将尾随空间存储在磁盘上，请参见行压缩实现。注意，页面压缩意味着行压缩。

#2

'n' represents support for unicode characters.
'n'表示支持unicode字符。
char - specifies string with fixed length storage. Space allocated with or without data present.
char -指定具有固定长度存储的字符串。有或没有数据的空间。
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.
文本——存储大量数据。为列存储分配的空间为16字节。

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

另外，varchar(max)和nvarchar(max)的文本和ntext已被弃用。

#3

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

不赞成使用varchar(max)和nvarchar(max)

#4

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

n前缀仅仅表示Unicode。除了使用Unicode文本外，它们的“n”类型与普通版本类似。

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

char是一个固定长度的字段。因此，填满“是”的char(10)仍然需要10字节的存储。

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

varchar是一个可变长域。使用“Yes”填充的char(10)将占用5字节的存储空间(使用var数据类型需要2字节的开销)。

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

字符(n)保存长度为x的字符串。varchar(n)保存长度为x. Storage = x+2字节的字符串。

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

vchar和nvarchar是相似的，只是每个字符只有2个字节。

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.

一般来说，在处理固定或半固定字符串时，应该只使用char和char(在varchar和nvarchar上)。一个很好的例子是product_code或user_type，它总是有n个字符长。

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

您不应该使用文本(或ntext)，因为它已经被弃用了。varchar(max)和nvarchar(max)提供了相同的功能。

#5

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

N前缀表示对unicode的支持，每个字符占用的字节是非unicode字符的两倍。

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

Varchar是可变长度。每个字段使用额外的2字节来存储长度。

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

字符长度是固定的。如果您知道您的数据将会有多长，那么使用char，因为您将节省字节!

Text is mostly deprecated in my experience.

根据我的经验，大多数情况下不赞成使用文本。

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

注意不要使用Varchar(max)和NVarchar(max)，因为这些字段不能被索引。

#6

I only know between "char" and "varchar".

我只知道“char”和“varchar”之间的关系。

char: it can allocate memory of specified size whether or not it is filled

char:它可以分配指定大小的内存，不管它是否被填充

varchar: it will allocate memory based on the number of characters in it but it should have some size called maximum size.

varchar:它将根据字符数来分配内存，但是它应该有一个称为最大大小的大小。

#7

Text is meant for very large amounts of text, and is in general not meant to be searchable (but can be in some circumstances. It will be slow anyway).

文本是为大量的文本而设计的，并且通常不打算被搜索(但是在某些情况下可以被搜索)。无论如何它将是缓慢的)。

The char/nchar datatypes are of fixed lenghts, and are padded if entered stuff is shorter, as opposed to the varchar/nvarchar types, which are variable length.

与可变长度的varchar/nvarchar类型不同，char/nchar数据类型具有固定的lenghts，如果输入的内容较短，则填充该类型。

The n types have unicode support, where the non-n types don't.

n种类型支持unicode，而非n种类型不支持。

#8

Text is deprecated.

文字是弃用。

Char is a set value. When you say char(10), you are reserving 10 characters for every single row, whether they are used or not. Use this for something that shouldn't change lengths (For example, Zip Code or SSN)

Char是一个集合值。当您说char(10)时，您将为每一行保留10个字符，不管它们是否被使用。对于不应该改变长度的东西(例如，邮政编码或SSN)

varchar is variable. When you say varchar(10), 2 bytes is set aside to store the size of the data, as well as the actual data (which might be only say, four bytes).

varchar是可变的。当您说varchar(10)时，会留出2个字节来存储数据的大小以及实际的数据(可能只有4个字节)。

The N represents uni-code. Twice the space.

N代表uni-code。空间的两倍。

#9

n-prefix: unicode. var*: variable length, the rest is fixed length.

n-prefix:unicode。可变长度，其余为固定长度。

All data types are properly and nicely... documented.

所有的数据类型都是正确的。记录。

Like here:

像在这里:

http://msdn.microsoft.com/en-us/library/ms187752.aspx

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

对于这些类型，是否真的有一个应用程序用例，或者它们中的一些只是被弃用了?

No, there is a good case for ANY of them.

不，他们中的任何一个都有好的理由。

#1