解决 SQL Server 最大列限制 1024 和 8kb 记录大小

Work around SQL Server maximum columns limit 1024 and 8kb record size

提问by Banketeshvar Narayan

I am creating a table with 1000 columns. Most of the columns are nvarchartype. Table is created, but with a warning

我正在创建一个包含 1000 列的表。大多数列都是nvarchar类型。表已创建，但有警告

Warning: The table "Test" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

警告：表“Test”已创建，但其最大行大小超过了允许的最大值 8060 字节。如果结果行超过大小限制，则对该表的 INSERT 或 UPDATE 将失败。

Most of the columns of the table already have data in it (i.e. 99% of columns have data). When I am trying to update any column after the 310th (where as all starting 309 columns having some value) it gives error:

表的大部分列已经有数据（即 99% 的列有数据）。当我尝试在第 310 列之后更新任何列时（因为所有开始的 309 列都有一些值），它会给出错误：

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

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

I am inserting this data to all starting 308 columns

我将此数据插入所有起始 308 列

"Lorem ipsum dolor sit amet, consectetur adipisicing elit."

“Lorem ipsum dolor 坐 amet，consectetur adipisicing 精英。”

When I am using ntextdata type then it is allowing me to update about 450 columns but beyond of that ntextis also not allowing me. I have to update at least 700 columns. Which SQL Server is not allowing to do that. I have the scenario that I cannot move some columns of table to another table.

当我使用ntext数据类型时，它允许我更新大约 450 列，但除此之外ntext也不允许我。我必须更新至少 700 列。哪个 SQL Server 不允许这样做。我的情况是我无法将表的某些列移动到另一个表。

Actually I am working for an existing window application. It's a very large windows application.

实际上，我正在为现有的窗口应用程序工作。这是一个非常大的窗口应用程序。

Actually the table in which I am trying to insert up to 700 nvarchar columns data is created dynamically at runtime. Only in some cases it requires to insert 400-600 columns. But generally it need 100 -200 columns which i am able to process easily.

实际上，我尝试在其中插入多达 700 个 nvarchar 列数据的表是在运行时动态创建的。只有在某些情况下才需要插入 400-600 列。但通常它需要 100 -200 列，我可以轻松处理。

The problem is that I cannot split this table in multiple tables. Because a lots of tables created with this structures and names of tables are maintained in another table i.e. there are more than 100 tables with this structure and they are being created dynamically. For creating the table and manipulating its data 4-5 languages(C#, Java..) are being used and WCF, Windows Service and Webservices also Involves.

问题是我无法将此表拆分为多个表。因为用这种结构创建的很多表和表的名称都保存在另一个表中，即有 100 多个具有这种结构的表并且它们是动态创建的。为了创建表和操作它的数据，使用了 4-5 种语言（C#、Java ..），WCF、Windows 服务和 Web 服务也涉及。

So I don't think that it would be easy manipulate the table and its data after splitting the table. If I split the table then it would require lots of structural changes.

所以我不认为在拆分表格后操作表格及其数据会很容易。如果我拆分表格，则需要进行大量结构更改。

So please suggest me that what would be the best way to solve this issue.

所以请建议我解决这个问题的最佳方法是什么。

I have also tried to use Sparse Columnlike:

我也尝试使用稀疏列，如：

Create table ABCD(Id int, Name varchar(100) Sparse, Age int);

I have also thought about ColumnStoreIndexbut my purpose is not solved.

我也考虑过ColumnStoreIndex但我的目的没有解决。

Sparse column allow me to create 3000 columns for a table but it also restrict me on page size.

稀疏列允许我为一个表创建 3000 列，但它也限制了我的页面大小。

Is any way to achieve it using some temporary table or by using any other type of SQL server object?

有没有办法使用一些临时表或使用任何其他类型的 SQL 服务器对象来实现它？

采纳答案by Kaushik Sharma

SQL Server Maximum Columns Limit

SQL Server 最大列数限制

Bytes per short string column 8,000

每个短字符串列的字节数 8,000

Bytes per GROUP BY, ORDER BY 8,060

每个 GROUP BY、ORDER BY 8,060 的字节数

Bytes per row 8,060

每行字节数 8,060

Columns per index key 16

每个索引键的列数 16

Columns per foreign key 16

每个外键的列数16

Columns per primary key 16

每个主键的列数16

Columns per nonwide table 1,024

每个非宽表的列数 1,024

Columns per wide table 30,000

每宽表的列数 30,000

Columns per SELECT statement 4,096

每个 SELECT 语句的列数 4,096

Columns per INSERT statement 4096

每个 INSERT 语句的列数 4096

Columns per UPDATE statement (Wide Tables) 4096

每个 UPDATE 语句的列数（宽表） 4096

When you combine varchar, nvarchar, varbinary, sql_variant,or CLR user-defined type columns that exceed 8,060bytes per row, consider the following:

当您组合每行超过8,060字节的varchar、nvarchar、varbinary、sql_variant或 CLR 用户定义类型列时，请考虑以下事项：

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.

超过 8,060 字节的行大小限制可能会影响性能，因为 SQL Server 仍然保持每页 8 KB 的限制。当 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型列的组合超过此限制时，SQL Server 数据库引擎会将宽度最大的记录列移动到 ROW_OVERFLOW_DATA 分配单元中的另一页，同时保持 24-原始页面上的字节指针。当记录根据更新操作被加长时，将大记录移动到另一个页面是动态的。缩短记录的更新操作可能会导致记录被移回 IN_ROW_DATA 分配单元中的原始页面。此外，查询和执行其他选择操作（例如对包含行溢出数据的大型记录进行排序或连接）会减慢处理时间，

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.

因此，在设计具有多个 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型列的表时，请考虑可能溢出的行的百分比以及可能查询此溢出数据的频率。如果可能对多行行溢出数据进行频繁查询，请考虑对表进行规范化，以便将某些列移动到另一个表中。然后可以在异步 JOIN 操作中查询。

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

• 对于 varchar、nvarchar、varbinary、sql_variant 和 CLR 用户定义类型的列，各个列的长度仍必须在 8,000 字节的限制内。只有它们的组合长度才能超过表的 8,060 字节行限制。
• 其他数据类型列（包括 char 和 nchar 数据）的总和必须在 8,060 字节的行限制内。大对象数据也不受 8,060 字节的行限制。
• 聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varchar 列。如果在 varchar 列上创建聚集索引并且现有数据位于 IN_ROW_DATA 分配单元中，则将数据推离行的对该列的后续插入或更新操作将失败。有关分配单位的详细信息，请参阅表和索引组织。
• 您可以将包含行溢出数据的列包含为非聚集索引的键列或非键列。
• 使用稀疏列的表的记录大小限制为 8,018 字节。当转换数据加上现有记录数据超过 8,018 字节时，将返回 MSSQLSERVER ERROR 576。在稀疏和非稀疏类型之间转换列时，数据库引擎会保留当前记录数据的副本。这暂时将记录所需的存储空间加倍。.
• 要获取有关可能包含行溢出数据的表或索引的信息，请使用 sys.dm_db_index_physical_stats 动态管理函数。

Creating table with n number of columns and datatype Nvarchar

创建具有 n 个列和数据类型 Nvarchar 的表

CREATE Proc [dbo].[CreateMaxColTable_Nvarchar500]
(@TableName nvarchar(100),@NumofCols int)
AS
BEGIN

DECLARE @i INT
DECLARE @MAX INT
DECLARE @SQL VARCHAR(MAX)
DECLARE @j VARCHAR(10)
DECLARE @len int
SELECT @i=1
SELECT @MAX=@NumofCols
SET @SQL='CREATE TABLE ' + @TableName + '('

WHILE @i<=@MAX

BEGIN
select @j= cast(@i as varchar)
SELECT @SQL= @SQL+'X'+@j  +' NVARCHAR(500) , '
SET @i = @i + 1
END
select @len=len(@SQL)

select  @SQL = substring(@SQL,0,@len-1)


SELECT @SQL= @SQL+ ' )'

exec (@SQL)

END

For more information you can visit these links:

有关更多信息，您可以访问以下链接：

http://msdn.microsoft.com/en-us/library/ms186981%28SQL.105%29.aspx?PHPSESSID=tn8k5p1s508cop8gr43e1f34d2

http://msdn.microsoft.com/en-us/library/ms186981%28SQL.105%29.aspx?PHPSESSID=tn8k5p1s508cop8gr43e1f34d2

http://technet.microsoft.com/en-us/library/ms143432.aspx

http://technet.microsoft.com/en-us/library/ms143432.aspx

But please could you tell the scenario why do you need a table with so much columns? I think you should consider about the re-design of the database.

但是请您说说为什么需要一个包含这么多列的表？我认为您应该考虑重新设计数据库。

回答by Martin Smith

This simply isn't possible. See Inside the Storage Engine: Anatomy of a record

这根本不可能。查看存储引擎内部：记录剖析

Assuming your table is something like this.

假设你的桌子是这样的。

CREATE TABLE T1(
    col_1 varchar(8000) NULL,
    col_2 varchar(8000) NULL,
    /*....*/
    col_999 varchar(8000) NULL,
    col_1000 varchar(8000) NULL
) 

Then even a row with all NULLvalues will use the following storage.

然后，即使是包含所有NULL值的行也将使用以下存储。

• 1 byte status bits A
• 1 byte status bits B
• 2 bytes column count offset
• 125 bytes NULL_BITMAP(1 bitper column for 1,000 columns)

• 1 字节状态位 A
• 1 字节状态位 B
• 2 字节列计数偏移
• 125 字节NULL_BITMAP（bit每列 1,000 列）

So that is a guaranteed 129 bytes used up already (leaving 7,931).

所以这保证 129 个字节已经用完（剩下 7,931 个）。

If any of the columns have a value that is not either NULLor an empty string then you also need space for

如果任何列的值既不是也不是NULL空字符串，那么您还需要空间

• 2 bytes variable length column count (leaving 7,929).
• Anywhere between 2 - 2000 bytes for the column offset array.
• The data itself.

• 2 字节可变长度列计数（剩余 7,929）。
• 列偏移数组的 2 - 2000 字节之间的任何位置。
• 数据本身。

The column offset array consumes 2 bytes per variable length column exceptif that column and all later columns are also zero length. So updating col_1000would force the entire 2000 bytes to be used whereas updating col_1would just use 2 bytes.

列偏移数组每个可变长度列消耗 2 个字节，除非该列和所有后续列的长度也为零。因此更新col_1000将强制使用整个 2000 字节，而更新 col_1将仅使用 2 个字节。

So you could populate each column with 5 bytes of data and when taking into account the 2 bytes each in the column offset array that would add up to 7,000 bytes which is within the 7,929 remaining.

因此，您可以用 5 个字节的数据填充每列，并考虑到列偏移数组中的每个 2 个字节，这将增加 7,000 个字节，即剩余的 7,929 个字节。

However the data you are storing is 102 bytes (51 nvarcharcharacters) so this can be stored off row with a 24 byte pointer to the actual data remaining in row.

但是，您要存储的数据是 102 字节（51 个nvarchar字符），因此可以将其存储在行外，并使用 24 字节指针指向行中剩余的实际数据。

FLOOR(7929/(24 + 2)) = 304

So the best case would be that you could store 304 columns of this length data and that is if you are updating from col_1, col_2, .... If col_1000contains data then the calculation is

所以最好的情况是你可以存储 304 列这个长度数据，也就是说，如果你从col_1, col_2,更新...。如果col_1000包含数据，则计算为

FLOOR(5929/24) = 247

For NTEXTthe calculation is similar except it can use a 16 byte pointerwhich would allow you to squeeze data into a few extra columns

对于NTEXT计算是相似的，除了它可以使用一个 16 字节的指针，这将允许您将数据压缩到几个额外的列中

FLOOR(7929/(16 + 2)) = 440

The need to follow all these off row pointers for any SELECTagainst the table would likely be highly detrimental to performance.

需要跟踪所有这些SELECT针对表的行外指针可能对性能非常不利。

Script to test this

测试这个的脚本

DROP TABLE T1

/* Create table with 1000 columns*/
DECLARE @CreateTableScript nvarchar(max) = 'CREATE TABLE T1('

SELECT @CreateTableScript += 'col_' + LTRIM(number) + ' VARCHAR(8000),'
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND 1000
ORDER BY number

SELECT @CreateTableScript += ')'

EXEC(@CreateTableScript)

/* Insert single row with all NULL*/
INSERT INTO T1 DEFAULT VALUES


/*Updating first 304 cols succeed. Change to 305 and it fails*/
DECLARE @UpdateTableScript nvarchar(max) = 'UPDATE T1 SET  '

SELECT @UpdateTableScript += 'col_' + LTRIM(number) + ' = REPLICATE(1,1000),'
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND 304
ORDER BY number

SET @UpdateTableScript = LEFT(@UpdateTableScript,LEN(@UpdateTableScript)-1)
EXEC(@UpdateTableScript)

回答by Stevan Trajkoski

Having table with 1.000 columns tells you that there is something very wrong in database design.

拥有 1.000 列的表告诉您数据库设计中存在一些非常错误的问题。

I have inherited project in which one of the tables had more than 500 columns and after more than one year I am still unable to significantly reduce it, because I will have to rework 90% of the application.

我继承了其中一个表有 500 多列的项目，一年多后我仍然无法显着减少它，因为我将不得不返工 90% 的应用程序。

So redesign your DB before it is too late.

因此，在为时已晚之前重新设计您的数据库。

回答by Atheer Mostafa

Max Columns per 'nonwide' table: 1,024 Max Columns per 'wide' table: 30,000

每个“非宽”表的最大列数：1,024 每个“宽”表的最大列数：30,000

Although what is exactly the case you require this number per single table ? It's highly recommended to partition your table vertically several times to get better performance and easier development.

尽管您需要每张桌子的这个数字究竟是什么情况？强烈建议对您的表进行多次垂直分区，以获得更好的性能和更轻松的开发。

回答by Manish

Creating table with n number of columns and datatype Nvarchar

创建具有 n 个列和数据类型 Nvarchar 的表

CREATE Proc [dbo].[CreateMaxColTable_Nvarchar500]
(@TableName nvarchar(100),@NumofCols int)
AS
BEGIN

DECLARE @i INT
DECLARE @MAX INT
DECLARE @SQL VARCHAR(MAX)
DECLARE @j VARCHAR(10)
DECLARE @len int
SELECT @i=1
SELECT @MAX=@NumofCols
SET @SQL='CREATE TABLE ' + @TableName + '('

WHILE @i<=@MAX

BEGIN
select @j= cast(@i as varchar)
SELECT @SQL= @SQL+'A'+@j  +' NVARCHAR(500) , '
SET @i = @i + 1
END
select @len=len(@SQL)

select  @SQL = substring(@SQL,0,@len-1)


SELECT @SQL= @SQL+ ' )'

exec (@SQL)

END

回答by joe

There are limits for each row in SQL server.

SQL Server 中的每一行都有限制。

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

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

gives details

提供细节

回答by Kunal

We had application which captures 5000 fields for a loan application. All fields are dependent on a single primary key loanid. We could have split the table into multiples but the fields are also dynamic. The admin also has a feature to create more fields. So everything is dynamic. They only good thing was a one to one relationship between loanid and fields.

我们有一个应用程序，它为贷款申请捕获了 5000 个字段。所有字段都依赖于一个主键loanid。我们可以将表拆分为多个，但字段也是动态的。管理员还具有创建更多字段的功能。所以一切都是动态的。他们唯一的好处是贷款和字段之间的一对一关系。

So, in the end we went with XML solution. The entire data is store in an xml document. Maximum flexibility but makes it diffifcult to query and report of.

所以，最后我们采用了 XML 解决方案。整个数据存储在一个 xml 文档中。最大的灵活性但使其难以查询和报告。