nchar 和 nvarchar (Transact-SQL)nchar and nvarchar (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

字符数据类型 nchar(大小固定)或 nvarchar(大小可变) 。Character data types that are either fixed-size, nchar, or variable-size, nvarchar. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 起,使用启用了补充字符 (SC) 的排序规则时,这些数据类型会存储 Unicode 字符数据的整个范围,并使用 UTF-16 字符编码。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), when a Supplementary Character (SC) enabled collation is used, these data types store the full range of Unicode character data and use the UTF-16 character encoding. 若指定了非 SC 排序规则,则这些数据类型仅会存储 UCS-2 字符编码支持的字符数据子集。If a non-SC collation is specified, then these data types store only the subset of character data supported by the UCS-2 character encoding.

参数Arguments

nchar [ ( n ) ]nchar [ ( n ) ]
固定大小字符串数据。Fixed-size string data. n 用于定义字符串大小(以双字节为单位),并且它必须为 1 到 4,000 之间的值 。n defines the string size in byte-pairs and must be a value from 1 through 4,000. 存储大小为 n 字节的两倍。The storage size is two times n bytes. 对于 UCS-2 编码,存储大小为 n 个字节的两倍,并且可存储的字符数也为 n。 For UCS-2 encoding, the storage size is two times n bytes and the number of characters that can be stored is also n. 对于 UTF-16 编码,存储大小仍为 n 个字节的两倍,但可存储的字符数可能小于 n,因为补充字符使用两个双字节(也称为代理项对)。 For UTF-16 encoding, the storage size is still two times n bytes but the number of characters that can be stored may be smaller than n because Supplementary Characters use two byte-pairs (also called surrogate-pair). nchar 的 ISO 同义词是 national char 和 national character 。The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ]nvarchar [ ( n | max ) ]
可变大小字符串数据。Variable-size string data. n 用于定义字符串大小(以双字节为单位),并且它可能为 1 到 4,000 之间的值 。n defines the string size in byte-pairs and can be a value from 1 through 4,000. max 指示最大存储大小是 2^30-1 个字符 (2 GB) 。max indicates that the maximum storage size is 2^30-1 characters (2 GB). 存储大小为 n 字节的两倍 + 2 个字节。The storage size is two times n bytes + 2 bytes. 对于 UCS-2 编码,存储大小为 n 个字节的两倍 + 2 个字节,并且可存储的字符数也为 n。 For UCS-2 encoding, the storage size is two times n bytes + 2 bytes and the number of characters that can be stored is also n. 对于 UTF-16 编码,存储大小仍为 n 个字节的两倍 + 2 个字节,但可存储的字符数可能小于 n,因为补充字符使用两个双字节(也称为代理项对)。 For UTF-16 encoding, the storage size is still two times n bytes + 2 bytes but the number of characters that can be stored may be smaller than n because Supplementary Characters use two byte-pairs (also called surrogate-pair). nvarchar 的 ISO 同义词是 national char varying 和 national character varying 。The ISO synonyms for nvarchar are national char varying and national character varying.

RemarksRemarks

一个常见误解是,认为在 NCHAR(n) 和 NVARCHAR(n) 中,n 定义字符数 。A common misconception is to think that NCHAR(n) and NVARCHAR(n), the n defines the number of characters. 但在 NCHAR(n) 和 NVARCHAR(n) 中,n 定义字符串的长度(以双字节为单位)(0-4,000) 。But in NCHAR(n) and NVARCHAR(n) the n defines the string length in byte-pairs (0-4,000). n 不会定义可存储的字符数 。n never defines numbers of characters that can be stored. 这类似于 CHAR(n) 和 VARCHAR(n) 的定义 。This is similar to the definition of CHAR(n) and VARCHAR(n).
出现此误解的原因是,使用在 Unicode 范围 0-65,535 中定义的字符时,每个双字节可以存储一个字符。The misconception happens because when using characters defined in the Unicode range 0-65,535, one character can be stored per each byte-pair. 但是,在较高的 Unicode 范围 (65,536-1,114,111) 中,一个字符可能使用两个双字节。However, in higher Unicode ranges (65,536-1,114,111) one character may use two byte-pairs. 例如,在定义为 NCHAR(10) 的列中,数据库引擎Database Engine 可以存储使用一个双字节(Unicode 范围 0-65,535)的 10 个字符,但在使用两个双字节(Unicode 范围 65,536-1,114,111)时,将不超过 10 个字符。For example, in a column defined as NCHAR(10), the 数据库引擎Database Engine can store 10 characters that use one byte-pair (Unicode range 0-65,535), but less than 10 characters when using two byte-pairs (Unicode range 65,536-1,114,111). 有关 Unicode 存储和字符范围的详细信息,请参阅 UTF-8 与 UTF-16 之间的存储差异For more information about Unicode storage and character ranges, see Storage differences between UTF-8 and UTF-16.

如果没有在数据定义或变量声明语句中指定 n ,则默认长度为 1。When n is not specified in a data definition or variable declaration statement, the default length is 1. 如果没有使用 CAST 函数指定 n ,则默认长度为 30。When n is not specified with the CAST function, the default length is 30.

若使用 nchar 或 nvarchar ,则建议:If you use nchar or nvarchar, we recommend to:

  • 如果列数据项的大小一致,则使用 nchar 。Use nchar when the sizes of the column data entries are consistent.
  • 如果列数据项的大小差异相当大,则使用 nvarchar 。Use nvarchar when the sizes of the column data entries vary considerably.
  • 如果列数据项大小相差很大,而且字符串长度可能超过 4,000 双字节,请使用 nvarchar(max) 。Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.

sysname 是系统提供的用户定义数据类型,除了不可为 Null 外,在功能上与 nvarchar(128) 相同 。sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname 用于引用数据库对象名 。sysname is used to reference database object names.

为使用 char 或 varchar 的对象分配的是默认的数据库排序规则,但可使用 COLLATE 子句分配特定的排序规则 。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 对于 nchar 和 nvarchar 始终为 ON 。SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF 不适用于 nchar 或 nvarchar 数据类型 。SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.

在 Unicode 字符字符串常量的前面加上字母 N 作为前缀来表示 UCS-2 或 UTF-16 输入,具体取决于是否使用了 SC 排序规则。Prefix a Unicode character string constants with the letter N to signal UCS-2 or UTF-16 input, depending on whether an SC collation is used or not. 如果没有 N 前缀,则字符串被转换为数据库的默认代码页,而该数据库可能无法识别某些字符。Without the N prefix, the string is converted to the default code page of the database that may not recognize certain characters. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,若使用启用了 UTF-8 的排序规则,则默认代码页能够存储 UNICODE UTF-8 字符集。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), when a UTF-8 enabled collation is used, the default code page is capable of storing UNICODE UTF-8 character set.

备注

在字符串常量前面添加字母 N 作为前缀时,如果要转换的常量不超过 nvarchar 字符串数据类型的最大长度 (4,000),则隐式转换将生成 UCS-2 或 UTF-16 字符串。When prefixing a string constant with the letter N, the implicit conversion will result in a UCS-2 or UTF-16 string if the constant to convert does not exceed the max length for the nvarchar string data type (4,000). 否则,隐式转换将生成大值 nvarchar(max)。Otherwise, the implicit conversion will result in a large-value nvarchar(max).

警告

每个非 null varchar(max) 或 nvarchar(max) 列都需要 24 个字节的附加固定分配,这将在执行排序操作期间根据 8,060 字节行限制进行计数 。Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation, which counts against the 8,060-byte row limit during a sort operation. 这些附加的字节可在表中为非 null varchar(max) 或 nvarchar(max) 列数创建隐式限制 。These additional bytes can create an implicit limit to the number of non-null varchar(max) or nvarchar(max) columns in a table. 在以下情况下不提供特殊错误:创建表格(最大行大小超过允许的最大 8,060 字节时出现的一般警告除外)时,或插入数据时。No special error is provided when the table is created (beyond the usual warning that the maximum row size exceeds the allowed maximum of 8,060 bytes) or at the time of data insertion. 此较大行大小可能会导致用户在某些正常操作期间未意料到的错误(如错误 512)。This large row size can cause errors (such as error 512) that users may not anticipate during some normal operations. 操作的两个示例是聚集索引密钥更新或完整列集排序。Two examples of operations are a clustered index key update, or sorts of the full column set.

转换字符数据Converting Character Data

有关转换字符数据的信息,请参阅 char 和 varchar (Transact-SQL)For information about converting character data, see char and varchar (Transact-SQL).

另请参阅See also

ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
COLLATE (Transact-SQL)COLLATE (Transact-SQL)
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
数据类型 (Transact-SQL)Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
LIKE (Transact-SQL)LIKE (Transact-SQL)
SET ANSI_PADDING (Transact-SQL)SET ANSI_PADDING (Transact-SQL)
SET @local_variable (Transact-SQL) SET @local_variable (Transact-SQL)
排序规则和 Unicode 支持 Collation and Unicode Support
单字节和多字节字符集Single-Byte and Multibyte Character Sets