我可以批量插入一個空的頁面壓縮表並獲得完全壓縮嗎？

我有很多大表（大約 1000 萬行寬）需要定期載入到 SQL Server 2016 中以進行只讀報告。我希望這些表在磁碟上盡可能小，這比載入或查詢的性能改進更重要。

這是我為不需要進一步索引的表所做的：

1. 用 . 創建表DATA_COMPRESSION=PAGE。
2. 使用 bcp 將平面文件中的數據批量插入到新表中。

表中的列類型是 varchar（從不超過 512，而不是最大值）、float、tinyint 或 date（不是 datetime）。所有列都被創建為可為空的，並且沒有定義主鍵或外鍵——它們對於查詢無關緊要，並且表永遠不會直接更新。一切的預設排序規則是SQL_Latin1_General_CP1_CI_AS.

當我這樣做時，我可以看到sys.allocation_units頁面數據壓縮已應用於堆，並且我可以看到sys.partitions填充因子正確為 0 (100%)。由於這些表比未壓縮的表要小得多，我認為壓縮已經完成。

但是，如果我隨後使用相同的選項進行重建DATA_COMPRESSION=PAGE，則應該已經壓縮的表會縮小大約 30%！看起來它從每頁數據大約 17 行變為每頁 25 行。（不過只有一次。在那之後再次重建並不比第一次重建更小。）

問題

所以我的問題是：（a）這裡發生了什麼？(b) 有沒有辦法在我載入表時直接獲得這個超小的壓縮大小，而無需在載入數據後重建？

@HandyD是完全正確的，我只想強調其他一些在插入堆時獲得壓縮的方法。

來自同一份文件

當堆配置為頁面級壓縮時，頁面僅通過以下方式接收頁面級壓縮：

• 數據是在啟用批量優化的情況下批量導入的。
• 使用 INSERT INTO … WITH (TABLOCK) 語法插入數據，並且該表沒有非聚集索引。
• 通過使用 PAGE 壓縮選項執行 ALTER TABLE … REBUILD 語句來重建表。

據此，您可以利用最少記錄的批量插入或使用INSERT INTO ... WITH (TABLOCK) 來獲得PAGE壓縮，而無需進行重建。

---

(a) 這裡發生了什麼？(b) 有沒有辦法在我載入表時直接獲得這個超小的壓縮大小，而無需在載入數據後重建？

PAGE插入堆時有一些規則來獲得壓縮，添加-h "TABLOCK"到你的bcp命令來獲得壓縮。

ROW壓縮在沒有這些先決條件的情況下工作，並且是以下範例中使用的最少壓縮量，感謝@DenisRubashkin指出這一點！

---

測試

範例啟動數據和 BCP 輸出命令

---

--Tested on SQL Server 2014 SP2

CREATE TABLE dbo.CompressedHeap_Source( Val varchar(512), 
                                Datefield Date, 
                                Tinyfield TinyINT,
                                Floatfield float) 
WITH (DATA_COMPRESSION = PAGE);

INSERT INTO dbo.CompressedHeap_Source
(
Val,Datefield,Tinyfield,Floatfield)

SELECT 'Bla',cast(getdate() as date),1,1.2412
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;

--bcp TEST.dbo.CompressedHeap_Source out E:\Data\HeapData.bcp -c -T

---

ROW壓縮和未壓縮大小

數據大小是在 132272 KB向堆中執行標準插入時，這是ROW壓縮但未PAGE壓縮。

沒有任何壓縮的數據大小是 ~176216 KB用於我們的測試。

exec sp_spaceused 'dbo.CompressedHeap_Source'

name                    rows                    reserved    data      index_size    unused
CompressedHeap_Source   6365530                 132296 KB   132272 KB   8 KB    16 KB

---

INSERT INTO … 使用製表符

插入WITH TABLOCK為我們提供了PAGE壓縮數據大小，69480 KB.

INSERT INTO dbo.CompressedHeap_Source2  WITH(TABLOCK)
(
Val,Datefield,Tinyfield,Floatfield)

SELECT 'Bla',cast(getdate() as date),1,1.2412
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2

---

批量插入

現在，當我們創建一個也被page壓縮的目標堆表並執行批量插入時with tablock：

CREATE TABLE dbo.CompressedHeap_Destination( Val varchar(512), 
                                Datefield Date, 
                                Tinyfield TinyINT,
                                Floatfield float) 
WITH (DATA_COMPRESSION = PAGE);

bulk insert dbo.CompressedHeap_Destination

from 'E:\Data\HeapData.bcp'  with (TABLOCK)

數據被page壓縮並且也在69480 KB：

name    rows    reserved    data    index_size  unused
CompressedHeap_Destination  6365530                 69512 KB    69480 KB    8 KB    24 KB

---

帶製表符的 BCP

BULK INSERT WITH TABLOCK通過使用BCP IN提示-h "TABLOCK"，您可以獲得與 相同的結果。這是有道理的，他們在內部做同樣的事情

--bcp TEST.dbo.CompressedHeap_Destination2 IN E:\Data\HeapData.bcp -c -T -h "TABLOCK"

結果大小為69480 KB

BCP 輸入無製表符

使用 BCP 從目標表副本中的同一文件載入數據

標準 bcp 命令會生成非壓縮數據：

--bcp TEST.dbo.CompressedHeap_Destination2 IN E:\Data\HeapData.bcp -c -T 

數據大小為132272 KB（行壓縮）。

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