Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
字符数据类型或者是固定长度的nchar,或者是可变长度的nvarchar,它们是unicode数据并且用UNICODE UCS-2字符集。
Arguments（参数）
-
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 nbytes. 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..
-
固定长度的Unicode字符串数据。n定义了字符串长度，并且n的值必须是从1到4000.nchar(n)所占的存储空间是2*n bytes。当nchar(n)比较(collation，比较，对照，校订) 代码页使用双字节字符集时，nchar(n)所占存储大小仍为n bytes。依据nchar(n)中存储的字符串，所占存储大小n bytes可能比定义的n值小。对应nchar的iso同义词(synonyms)是national char 和national character。
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.
-
可变长度的Unicode字符串数据。n定义了字符串长度，n可以被赋以1到4000之间的值。max表示最大的存储大小是2^31-1 bytes(2GB)。按bytes(字节)表示的存储大小是输入数据实际长度的2倍加2bytes.nvarchar对应iso中的同义词是national char varying 和national character varying。
Remarks（备注）
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
当在数据定义和变量声明的时候，如果n没有定义，默认n是1.当使用cast函数时，如果n没有定义，则默认n是30
Use nchar when the sizes of the column data entries are probably going to be similar.
当某一列中的数据实体的大小是相似的时候，用nchar类型。
Use nvarchar when the sizes of the column data entries are probably going to vary considerably.
当某一列种的数据实体的大小是变化相当大(considerably)的时候，使用nvarchar
sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.
sysname是一种系统提供的，用户定义的数据类型，它功能上与 nvarchar(128)是等价的，除了sysname类型是不允许为null的之外。sysname类型被用于应用数据库中的对象名。
Objects that use nchar or nvarchar are assigned the default collation of the database unless a specific collation is assigned using the COLLATE clause.
使用nchar或者nvarchar类型的对象被分配数据库默认的collation（比对），除非使用COLLATE子句分配了明确的collation给这个对象。
SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.
对于nchar和nvarchar数据类型来说，ANSI_PADDING（控制字符串尾随空格是否被剪裁，ON不剪裁，OFF剪裁）开关始终是ON的。SET ANSI_PADDING OFF语句不能被应用到nchar或者是nvarchar数据类型上。
Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
Unicode字符串常量使用N前缀表示，如果没有N前缀，字符串将被转换为数据库的默认代码页。但是，这个默认的代码页可能不能识别这个指定的字符。
这个的意思就是：如有两个常量字符串：'你好'，N'你好'，前面那个就表示是你数据库中默认代码页的字符串，而后面是以unicode代码编码。
查看当前服务器的默认collation: PRINT CONVERT(SYSNAME, SERVERPROPERTY('collation'))
查看当前服务器的默认代码页：SELECT COLLATIONPROPERTY(CONVERT(SYSNAME, SERVERPROPERTY('collation')),'CodePage')
 Warning |
|---|
| 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. This can create an implicit limit to the number of non-null varchar(max) or nvarchar(max) columns that can be created in a table. No special error is provided when the table is created (beyond the usual warning that the maximum row size exceeds the allowed maximum of 8060 bytes) or at the time of data insertion. This large row size can cause errors (such as error 512) during some normal operations, such as a clustered index key update, or sorts of the full column set, which users cannot anticipate until performing an operation. 每个非空的varchar(max) 或者nvarchar(max)列，都需要额外的24bytes的固定存储空间分配。这个额外的分配可能违反了在排序操作期间行不能超出8060的限制。 对于创建一个表来说，能创建非空的varchar(max)和nvarchar(max)列的数目这可能是一个很不明显的限制。这样的表在创建或者插入数据的时候没有提供明确的错误（除了一个一般性的警告“the maximum row size exceeds the allowed maximum of 8060 bytes”）。这种巨大的行的大小在一些正常的操作（例如：一个clustered index key整个列集的更新，或者排序）期间可能导致错误（例如：error 512），这样的错误是用户无法预测或者猜测到的，直到他们执行了这些操作，产生了错误时候才能发现。 |
SQL Server Maximum Columns Limit
Bytes per short string column 8,000
Bytes per GROUP BY, ORDER BY 8,060
Bytes per row 8,060
Columns per index key 16
Columns per foreign key 16
Columns per primary key 16
Columns per nonwide table 1,024
Columns per wide table 30,000
Columns per SELECT statement 4,096
Columns per INSERT statement 4096
Columns per UPDATE statement (Wide Tables) 4096
When you combine varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns that exceed 8,060 bytes per row, consider the following:
Surpassing the 8,060-byte row-size limit might affect performance because SQL Server still maintains a limit of 8 KB per page. When a combination of varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns exceeds this limit, the SQL Server Database Engine moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, while maintaining a 24-byte pointer on the original page. Moving large records to another page occurs dynamically as records are lengthened based on update operations. Update operations that shorten records may cause records to be moved back to the original page in the IN_ROW_DATA allocation unit. Also, querying and performing other select operations, such as sorts or joins on large records that contain row-overflow data slows processing time, because these records are processed synchronously instead of asynchronously.
Therefore, when you design a table with multiple varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns, consider the percentage of rows that are likely to flow over and the frequency with which this overflow data is likely to be queried. If there are likely to be frequent queries on many rows of row-overflow data, consider normalizing the table so that some columns are moved to another table. This can then be queried in an asynchronous JOIN operation.
- The length of individual columns must still fall within the limit of 8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR user-defined type columns. Only their combined lengths can exceed the 8,060-byte row limit of a table.
- The sum of other data type columns, including char and nchar data, must fall within the 8,060-byte row limit. Large object data is also exempt from the 8,060-byte row limit.
- The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. For more information about allocation units, see Table and Index Organization.
- You can include columns that contain row-overflow data as key or nonkey columns of a nonclustered index.
- The record-size limit for tables that use sparse columns is 8,018 bytes. When the converted data plus existing record data exceeds 8,018 bytes, MSSQLSERVER ERROR 576 is returned. When columns are converted between sparse and nonsparse types, Database Engine keeps a copy of the current record data. This temporarily doubles the storage that is required for the record. .
- To obtain information about tables or indexes that might contain row-overflow data, use the sys.dm_db_index_physical_stats dynamic management function.
For more information you can visit these links:
http://technet.microsoft.com/en-us/library/ms143432.aspx
85
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
