带有 varchar(max) 字段的 SQL Server 2012 中的最大行大小 [英] Max Row Size in SQL Server 2012 with varchar(max) fields

问题描述

我创建了一个列类型为 nvarchar(max) 的表，我的理解是它们可以支持 2GB.但是在插入时，我仍然收到此错误:

I have created a table with column types as nvarchar(max), which my understanding is that they can support 2GB. However on inserting, I still receive this error:

无法创建大于允许的最大行大小 8060 的大小为 8061 的行.

Cannot create a row of size 8061 which is greater than the allowable maximum row size of 8060.

是否需要对数据库进行全局设置，或者我遇到了其他限制?每个表的 varchar(max) 字段数有限制吗?

Is there a global setting on the database required, or is there another limit I am hitting? Is there a limit to the number of varchar(max) fields per table?

推荐答案

SQL server 使用页面来存储数据.页面大小为 8kb.

所以 SQL Server 中的记录大小(行大小)不能大于 8060 字节.

如果数据不适合 8060 字节，则使用引用指针.当 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型列的组合超过此限制时，SQL Server 数据库引擎会将宽度最大的记录列移动到 ROW_OVERFLOW_DATA 分配单元中的另一页，同时保持 24 字节指针在原始页面上.

If data is not fitted in 8060 bytes then reference pointers are used. 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.

将大记录移动到另一个页面是动态的，因为记录会根据更新操作被加长.缩短记录的更新操作可能会导致记录被移回 IN_ROW_DATA 分配单元中的原始页面.

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.

使用稀疏列的表的记录大小限制为 8,018 字节.当转换数据加上现有记录数据超过 8,018 字节时，将返回 MSSQLSERVER ERROR 576.在稀疏和非稀疏类型之间转换列时，数据库引擎会保留当前记录数据的副本.这暂时将记录所需的存储空间加倍..

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

要获取有关可能包含行溢出数据的表或索引的信息，请使用 sys.dm_db_index_physical_stats 动态管理函数.

To obtain information about tables or indexes that might contain row-overflow data, use the sys.dm_db_index_physical_stats dynamic management function.

这篇关于带有 varchar(max) 字段的 SQL Server 2012 中的最大行大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！