SQL Server 2008 稀疏欄位最佳實務：何時轉換為 SPARSE 節省空間

稀疏欄位特性

• NULL 值**零儲存空間** (不佔用頁面位元組)。
• 非 NULL 值額外 **+4 位元組** 開銷 (固定長度型別)。
• 僅支援固定長度型別 (int, datetime, money, uniqueidentifier 等)，不支援 varchar(max), xml, geometry 等 LOB 型別。
• 可包含於篩選索引 (WHERE col IS NOT NULL) 大幅縮小索引體積。

轉換決策：NULL 比例門檻值

每個資料型別有「平衡點」，超過此 NULL 比例才節省空間：

自動偵測腳本：找出適合轉為 SPARSE 的欄位

USE AdventureWorks;
GO
SET NOCOUNT ON;

CREATE TABLE #SparseThreshold (DataType VARCHAR(50), PctNull INT);
INSERT INTO #SparseThreshold VALUES
('bit',98),('tinyint',86),('smallint',76),('int',64),('bigint',52),
('real',64),('float',52),('smallmoney',64),('money',52),
('smalldatetime',64),('datetime',52),('uniqueidentifier',43),('date',69);

CREATE TABLE #NullStats (ColName VARCHAR(500), NullCount INT, DataType VARCHAR(50), TotalRows INT);

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'INSERT INTO #NullStats SELECT ''' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) + N'.' + QUOTENAME(COLUMN_NAME) + N''', COUNT(*), ''' + DATA_TYPE + N''', (SELECT COUNT(*) FROM ' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) + N') FROM ' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) + N' WHERE ' + QUOTENAME(COLUMN_NAME) + N' IS NULL;'
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN sys.objects o ON c.TABLE_NAME = o.name AND c.TABLE_SCHEMA = SCHEMA_NAME(o.schema_id)
WHERE o.type = 'U' AND c.DATA_TYPE IN (SELECT DataType FROM #SparseThreshold);
-- 可加 WHERE 限定 schema/table

EXEC sp_executesql @sql;

SELECT n.ColName, n.NullCount, n.TotalRows,
       CAST(n.NullCount * 1.0 / n.TotalRows * 100 AS DECIMAL(5,2)) AS NullPct,
       ISNULL(t.PctNull, 60) AS ThresholdPct,
       CASE WHEN n.NullCount * 1.0 / n.TotalRows >= ISNULL(t.PctNull, 60) * 0.01 THEN '建議 SPARSE' ELSE '不建議' END AS Recommendation
FROM #NullStats n
LEFT JOIN #SparseThreshold t ON n.DataType = t.DataType
WHERE n.NullCount > 0
ORDER BY NullPct DESC;

DROP TABLE #NullStats, #SparseThreshold;

實際轉換語法

-- 新增稀疏欄位
CREATE TABLE CustomerInfo (
    CustomerID INT PRIMARY KEY,
    Address_Line1 VARCHAR(100) NOT NULL,
    Address_Line6 VARCHAR(100) SPARSE NULL  -- 直接宣告
);

-- 現有欄位轉換 (需重建表/欄位，因無 ALTER COLUMN ... SPARSE)
-- 方法: 新增稀疏欄位 → 更新資料 → 刪除舊欄位 → 重命名
ALTER TABLE CustomerInfo ADD Address_Line6_New VARCHAR(100) SPARSE NULL;
UPDATE CustomerInfo SET Address_Line6_New = Address_Line6;
ALTER TABLE CustomerInfo DROP COLUMN Address_Line6;
EXEC sp_rename 'CustomerInfo.Address_Line6_New', 'Address_Line6', 'COLUMN';

應用場景範例

客戶地址表：Line1-3 必填，Line4-5 常用，Line6-8 極少用 (如 5% 填寫)。
→ Line6-8 宣告為 SPARSE，Line4-5 保持一般欄位。

限制與注意

• 稀疏欄位**不能**有預設值、規則、IDENTITY、ROWGUIDCOL。
• 不能為計算欄位一部分，也不能參與叢集索引鍵 (可在非叢集索引/篩選索引)。
• 最大欄位數限制：一般 1024，稀疏欄位最多 30,000 (但總資料列大小仍受 8060 位元組限制，稀疏欄位非 NULL 時計入)。
• 壓縮 (ROW/PAGE) 與稀疏欄位**互斥**，同一資料表/分區只能二擇一。