大型 SQL Server 資料表清洗與資料型別優化實戰

在實際專案中，我們經常會面對從異構系統遷移而來、結構混亂的龐大資料表。最近我處理了一張擁有 6,200 萬列、超過 500 個欄位的表格，原始資料全部以 varchar(50) 儲存，僅有主鍵欄位設有適當型別。這篇文章記錄了我如何系統性地清洗資料、優化資料型別，並利用動態 SQL 高效分析欄位長度的完整過程。

核心問題：萬惡的「全字串」設計

當所有業務欄位都塞進 varchar(50) 時，會引發連鎖反應：

• 日期無法進行範圍查詢或運算
• 數值比較需隱含轉換，導致索引失效
• 布林邏輯欄位（Y/N）無法建立高效索引
• 儲存空間浪費，I/O 壓力劇增

分階段清洗策略

1. 日期欄位：轉為整數型

發現大量 YYYYMMDD 格式日期字串，直接轉為 INT 型別。好處是：

• 佔用 4 Bytes，比 DATE（3 Bytes）略大，但排序、篩選、分區皆極高效
• 避免字串比較的語系/排序規則陷阱
• 便於建立分區函數

2. 數值欄位：統一轉為 INT / BIGINT

掃描所有疑似數值的欄位，批次 ALTER COLUMN 為整數型別。執行前務必以 TRY_CAST 驗證髒資料，避免轉換失敗中斷作業。

3. 布林旗標：拒絕 BIT，改用 CHAR(1)

雖然 BIT 看似天生適合 Y/N/NULL，但它有個致命缺點：無法建立單欄索引（僅能包含在複合索引中）。改為 CHAR(1) NULL 後，可自由建立篩選索引（Filtered Index），查詢效能大幅提升。

4. 郵遞區號：拆分定長字串

原始資料為「5 碼 + 4 碼」兩欄，分別轉為 CHAR(5) 與 CHAR(4)，皆允許 NULL。定長字串在頁面儲存上比 VARCHAR 更可預測，亦避免前導零消失。

5. 不明業務欄位：保留 VARCHAR(50) 但建立長度基線

對於無法確認業務含義的欄位，暫維持原狀，但必須掌握實際資料最大長度，為後續型別收斂提供依據。

關鍵技巧：動態 SQL 批量掃描 500+ 欄位最大長度

逐欄手寫 SELECT MAX(LEN(col)) 顯然不切實際。利用 INFORMATION_SCHEMA.COLUMNS 產生動態查詢，一次完成全表掃描：

-- 1. 產生 SELECT 子句清單
SELECT 
    ', MAX(LEN(' + QUOTENAME(COLUMN_NAME) + ')) AS ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblArthurClean'
  AND DATA_TYPE IN ('char', 'varchar');

將結果複製到編輯器，前綴加上 SELECT，結尾加上 FROM tblArthurClean，再包裝計時區塊：

SELECT GETDATE();
GO
-- 動態產生的 MAX(LEN(...)) 清單
FROM tblArthurClean;
SELECT GETDATE();
GO

實測效能與收穫

在標準伺服器上，完整掃描 6,200 萬列 × 數百欄位僅耗時 約 1 小時 15 分鐘。輸出的單列結果集瞬間給出每欄實際最大長度，讓我能：

1. 精準制定 ALTER COLUMN VARCHAR(n) 收斂腳本
2. 識別出超長異常值，反向追查上游系統 Bug
3. 為後續建立索引、分區、壓縮提供科學依據

心法總結

「凡是 SQL 能自動完成的事，就別讓人肉去做。」

善用系統目錄檢視與動態 SQL，將重複性 DDL/DML 轉化為可控、可重現、可版管的程式碼，是資料工程師從「寫腳本」進化為「建系統」的關鍵分水嶺。