SQL Server 2014 壓縮和最大行大小

我需要創建具有大量十進制（26,8）列的寬非規範化表（少於 1024 列限制，大多數列將為空或零）。我知道每行限制大約 8060 字節，所以我嘗試使用頁面壓縮創建表。下面的程式碼創建表，插入一行並查詢行大小。行大小遠低於限制，但如果我嘗試向表中添加一個十進制 (26,8) 列，操作將失敗並出現錯誤“創建或更改表 ’t1’ 失敗，因為最小行大小為 8074，包括 1256字節的內部成本。”。有什麼方法可以創建包含這麼多列的單個表？

drop table t1
GO
create table t1(c1 decimal(26, 8) null)
with (data_compression = page)
GO

declare @i int = 2;
declare @sql varchar(100);
while @i <= 486
begin
   set @sql = 'alter table t1 add c' + convert(varchar, @i) + ' decimal(26, 8) null';
   execute (@sql);
   set @i += 1;
end;
GO


insert into t1(c1) select 0
GO
declare @i int = 2;
declare @sql varchar(100);
while @i <= 486
begin
   set @sql = 'update t1 set c' + convert(varchar, @i) + ' = 0';
   execute (@sql);
   set @i += 1;
end;
GO

select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
GO

您遇到的限制與頁面上儲存的數據無關。計算是根據列的數據類型完成的。這就是為什麼您在表中沒有任何數據的情況下遇到錯誤。壓縮使這個限制變得更糟。您可以在此處閱讀有關成本背後的技術細節。

您可以使用SPARSE列解決此問題。這意味著插入可能會失敗，具體取決於您插入的內容，但您可以繞過 8060 字節限制。以下程式碼顯示您可以創建 1023 列就好了：

drop table t1
GO
create table t1(c1 decimal(26, 8) null)
GO

declare @i int = 2;
declare @sql varchar(100);
while @i <= 1023
begin
   set @sql = 'alter table t1 add c' + convert(varchar, @i) + ' decimal(26, 8) SPARSE null';
   execute (@sql);
   set @i += 1;
end;
GO

但是，它周圍的所有限制（閱讀連結的文章）可能會使它不適合您的案例。具體來說，只有NULL值（不是0）經過優化以佔用很少的空間。0如果您嘗試在單行中插入太多s，您將收到錯誤消息。這是我嘗試插入 1023 個0值時看到的內容：

消息 511，級別 16，狀態 1，行 1 無法創建大小為 17402 的行，該行大於允許的最大行大小 8060。

我想如果你真的很絕望，你可以創建列VARCHAR(27)。可以將可變長度列移出頁面，以便您可以超過表定義中的 8060 字節限制，但插入某些值組合將失敗。SQL Server 在創建表時會警告您：

警告：表“t1”已創建，但其最大行大小超過了允許的最大值 8060 字節。如果結果行超過大小限制，則對該表的 INSERT 或 UPDATE 將失敗。

VARCHAR(27)如果您採用這種方法，頁面或行壓縮可能會有所幫助。這將最大限度地減少 和 使用的0空間NULL。我VARCHAR(27)能夠插入 1023 個0值就好了。

引用自：https://dba.stackexchange.com/questions/177909