解决 SQL Server 最大列限制 1024 和 8kb 记录大小 [英] Work around SQL Server maximum columns limit 1024 and 8kb record size
问题描述
我正在创建一个包含 1000 列的表.大多数列是 nvarchar 类型.表已创建，但有警告
I am creating a table with 1000 columns. Most of the columns are nvarchar type. Table is created, but with a warning
警告:表Test"已创建，但其最大行大小超过允许的最大值 8060 字节.对此进行插入或更新如果结果行超过大小限制，表将失败.
表格的大部分列已经有数据(即 99% 的列有数据).当我尝试在第 310 列之后更新任何列(因为所有开始的 309 列都有一些值)时，它会给出错误:
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:
无法创建大于允许值的 8061 行最大行大小为 8060.
我将此数据插入到所有起始 308 列
I am inserting this data to all starting 308 columns
Lorem ipsum dolor sat amet，consectetur adipisicing 精英."
"Lorem ipsum dolor sit amet, consectetur adipisicing elit."
当我使用 ntext 数据类型时，它允许我更新大约 450 列，但除此之外 ntext 也不允许我.我必须更新至少 700 列.哪个 SQL Server 不允许这样做.我遇到了无法将表的某些列移动到另一个表的情况.
When I am using ntext data type then it is allowing me to update about 450 columns but beyond of that ntext is 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.
实际上我正在为现有的窗口应用程序工作.这是一个非常大的 Windows 应用程序.
Actually I am working for an existing window application. It's a very large windows application.
实际上，我尝试在其中插入多达 700 个 nvarchar 列数据的表是在运行时动态创建的.只有在某些情况下才需要插入 400-600 列.但通常它需要 100 -200 列，我可以轻松处理.
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.
问题是我无法将此表拆分为多个表.因为使用这种结构创建的许多表和表的名称都保存在另一个表中，即有 100 多个具有这种结构的表并且它们是动态创建的.为了创建表和操作它的数据，使用了 4-5 种语言(C#、Java ..)，WCF、Windows 服务和 Web 服务也涉及.
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.
所以我不认为在拆分表格后操作表格及其数据会很容易.如果我拆分表格，则需要进行大量结构更改.
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.
我也尝试使用稀疏列，例如:
Create table ABCD(Id int, Name varchar(100) Sparse, Age int);
我也考虑过 ColumnStoreIndex 但我的目的没有解决.
I have also thought about ColumnStoreIndex but my purpose is not solved.
稀疏列允许我为一个表创建 3000 列，但它也限制了我的页面大小.
Sparse column allow me to create 3000 columns for a table but it also restrict me on page size.
有什么办法可以使用一些临时表或使用任何其他类型的 SQL 服务器对象来实现它吗?
Is any way to achieve it using some temporary table or by using any other type of SQL server object?
推荐答案
SQL Server 最大列数限制
每个短字符串列的字节数 8,000
Bytes per short string column 8,000
每个 GROUP BY、ORDER BY 的字节数 8,060
Bytes per GROUP BY, ORDER BY 8,060
每行字节数8,060
每个索引键的列16
每个外键的列16
每个主键的列16
每个非宽表的列1,024
每个宽表的列数 30,000
每个 SELECT 语句的列4,096
Columns per SELECT statement 4,096
每个 INSERT 语句的列数 4096
Columns per INSERT statement 4096
每个 UPDATE 语句的列数(宽表)4096
Columns per UPDATE statement (Wide Tables) 4096
当您组合 varchar、nvarchar、varbinary、sql_variant、 或 CLR 用户定义类型列且每行超过 8,060 字节时，请考虑以下事项:
When you combine varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns that exceed 8,060 bytes per row, consider the following:
超过 8,060 字节的行大小限制可能会影响性能，因为 SQL Server 仍然保持每页 8 KB 的限制.当 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型列的组合超过此限制时，SQL Server 数据库引擎会将宽度最大的记录列移动到 ROW_OVERFLOW_DATA 分配单元中的另一页，同时保持 24-原始页面上的字节指针.当记录根据更新操作被加长时，将大记录移动到另一个页面是动态的.缩短记录的更新操作可能会导致记录被移回 IN_ROW_DATA 分配单元中的原始页面.此外，查询和执行其他选择操作(例如对包含行溢出数据的大型记录进行排序或连接)会减慢处理时间，因为这些记录是同步处理的，而不是异步处理的.
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.
因此，当您设计具有多个 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型列的表时，请考虑可能溢出的行的百分比以及这种溢出数据可能发生的频率被查询.如果可能对多行行溢出数据进行频繁查询，请考虑对表进行规范化，以便将某些列移动到另一个表中.然后可以在异步 JOIN 操作中查询.
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.
- 单个列的长度必须仍然在8,000 字节用于 varchar、nvarchar、varbinary、sql_variant 和 CLR用户定义的类型列.只有它们的总长度可以超过表的行数限制为 8,060 字节.
- 其他数据类型列的总和，包括char和nchar数据，必须在 8,060 字节的行限制内.大对象数据也是免于 8,060 字节的行限制.
- 聚集索引的索引键不能包含 varchar 列在 ROW_OVERFLOW_DATA 分配单元中有现有数据.如果在 varchar 列和现有的列上创建聚集索引数据在 IN_ROW_DATA 分配单元中，后续插入或将数据推离行的列上的更新操作将失败.有关分配单位的详细信息，请参阅表和索引组织.
- 您可以将包含行溢出数据的列作为键或非聚集索引的非键列.
- 使用稀疏列的表的记录大小限制为 8,018字节.当转换数据加上现有记录数据超过8,018 字节，返回 MSSQLSERVER 错误 576.当列是在稀疏和非稀疏类型之间转换，数据库引擎保持一个当前记录数据的副本.这暂时使存储加倍这是记录所必需的..
- 获取有关可能包含的表或索引的信息行溢出数据，使用 sys.dm_db_index_physical_stats 动态管理功能.
创建包含 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://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.
这篇关于解决 SQL Server 最大列限制 1024 和 8kb 记录大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
