bigint 表的儲存大小

我正在使用具有這種格式的表：

CREATE TABLE dbo.ID_STORE
( WORKING_ID bigint PRIMARY KEY CLUSTERED )

該表儲存了大約 200 萬行，但儲存的 id 不是連續的，MAX(WORKING_ID)-MIN(WORKING_ID) 大約是 2400 萬。

當我查看已用空間時，我發現大約 57 兆字節，而我預計略高於 2 10^6 x 8 = 16 兆字節。誰能解釋其中的區別？

編輯：這些數字是從第一次導入到所述表中獲得的。該表在填充之前也會被截斷。

以 FixedVar 格式（預設）儲存時，每行至少有 7 個字節的成本。還會有（通常相對較少）數量的頁面用於聚集索引的上層。優化儲存，不考慮較高的索引級別，200 萬行將需要剛剛超過：

(7 + 8 bytes) * 2,000,000 = 28.61MB.

更重要的是，頁面可能已經拆分（除非數據是按集群鍵順序載入的），因此目前頁面可能不會 100% 滿。拆分頁面時，為了按鍵順序容納新行，大約 50% 的現有行被移動到新頁面，從而降低了平均密度。此外，如果刪除行的整個頁面變為空，則任何刪除的行只會導致空間被回收。此外，每個 8KB 數據頁有一個 96 字節的標題，頁上每行 2 個字節用於行偏移數組。

以下範例載入 2,000,000 行，其分佈與您的數據大致相同，並儘可能合理地壓縮：

CREATE TABLE dbo.ID_STORE
( 
   WORKING_ID bigint NOT NULL PRIMARY KEY CLUSTERED
);

WITH
 L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
 L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
 L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
 L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
 L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
 L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
 Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12
FROM Nums
WHERE Nums.n <= 2000000;

輸出：

EXECUTE sys.sp_spaceused 
   @objname = N'dbo.ID_STORE',
   @updateusage = 'true';

…顯示為該對象保留的 33,800 KB 空間。

截斷並載入，因此發生頁面拆分：

TRUNCATE TABLE dbo.ID_STORE;

WITH
 L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
 L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
 L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
 L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
 L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
 L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
 Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12
FROM Nums
WHERE Nums.n <= 1000000;

WITH
 L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
 L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
 L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
 L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
 L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
 L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
 Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12 + 1
FROM Nums
WHERE Nums.n <= 1000000;

輸出：

EXECUTE sys.sp_spaceused 
   @objname = N'dbo.ID_STORE',
   @updateusage = 'true';

…現在顯示已保留 50,632 KB KB 空間。

重建聚集索引：

ALTER INDEX ALL 
ON dbo.ID_STORE
REBUILD 
WITH 
(
   MAXDOP = 1, 
   SORT_IN_TEMPDB = ON
);

…再次將保留的空間減少到 33,800 KB。

根據您擁有的 SQL Server 的版本和版本，可以使用行或頁面壓縮、聚集列儲存或聚集列儲存歸檔儲存更緊湊地儲存此表。在後一種情況下（需要 SQL Server 2014 Enterprise），2,000,000 行僅保留 2,960 KB 的空間。

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