資料庫開發者常認為 ALTER TABLE ... ALTER COLUMN 只是修改中繼資料。實際上，修改固定長度欄位長度 (如 CHAR(985) → CHAR(1000)) 會觸發資料列重寫、頁面分裂、轉發指標產生、空間嚴重膨脹。本文以實測 DBCC IND/DBCC PAGE 揭示底層儲存行為。
USE Test;
GO
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb;
GO
CREATE TABLE tb (id INT IDENTITY(1,1), col CHAR(985));
INSERT INTO tb SELECT 'aaaa' UNION ALL SELECT 'bbbb' UNION ALL SELECT 'cccc'
UNION ALL SELECT 'dddd' UNION ALL SELECT 'eeee' UNION ALL SELECT 'ffff'
UNION ALL SELECT 'gggg' UNION ALL SELECT 'hhhh' UNION ALL SELECT 'iiii'
UNION ALL SELECT 'jjjj'; -- 10 列
EXEC sp_spaceused 'tb';
-- 結果：24 KB (資料 16 KB + 索引 8 KB)，佔 2 頁 (8K×2)
ALTER TABLE tb ALTER COLUMN col CHAR(1000);
EXEC sp_spaceused 'tb';
-- 結果：32 KB (資料 24 KB + 索引 8 KB)，佔 3 頁！
DBCC IND(Test, tb, 0); -- 0 = 僅列內資料頁 + IAM
關鍵欄位：PageFID (檔案號)、PagePID (頁號)、PageType (1=資料頁)。
DBCC TRACEON(3604); -- 輸出導向用戶端
DBCC PAGE(Test, 1, 114, 1); -- 檔案1, 頁114, 選項1(列標頭+資料+位移表)
Slot 0, Offset 0x60 (96), Length 996 -- 列標頭 96 bytes 後開始 Slot 1, Offset 0x444 (1092), Length 996 ... Slot 7, Offset 0x1B9C (7068), Length 996 -- 8 列滿 OFFSET TABLE: 7→7068, 6→6072, ..., 0→96
Slot 0, Offset 0x60, Length 9 -- 原 Slot 0 變成 9 bytes 轉發指標！ 00000000: 04 AF0000 00 010002 00 -- 04=狀態, AF0000=頁面175, 0100=檔案1, 0200=Slot 2 Slot 1, Offset 0x69, Length 1996 -- 原 Slot 1 變雙倍長！ 00000000: 10 00C907 02000000 62626262... (bbbb 出現兩次！) ... 0300 -- 欄位計數變 3 (原 2 + 新增 1)
0300 顯示現有 3 個欄位 (id, col_old, col_new)。Slot 0 (原 Slot 0 aaaa), Offset 0x1014, Length 1996 Slot 1 (原 Slot 8 iiii), Offset 0x17E0, Length 1996 Slot 2 (原 Slot 9 jjjj), Offset 0x60, Length 2010 -- 多 14 bytes！ Slot 3 (原 Slot 2 cccc), Offset 0x83A, Length 2010 -- 多 14 bytes！ OFFSET TABLE: 3→2106, 2→96, 1→6112, 0→4116
Slot 0 (原 Slot 4 eeee), Offset 0x452, Length 2010 Slot 1 (原 Slot 6 gggg), Offset 0x101E, Length 2010 OFFSET TABLE: 1→4126, 0→1106
| 列類別 | 原位置殘留 | 新位置結構 | 長度變化 |
|---|---|---|---|
| 未分裂留在原頁 (bbbb~hhhh) | 無 | 原列 + 新欄位 (1000 bytes) | 996 → 1996 (+1000) |
| 分裂移至新頁 (aaaa, cccc, eeee, gggg) | 9 bytes 轉發指標 (狀態+目標頁+檔案+Slot) | 原列 + 新欄位 + 14 bytes 回溯指標 (原頁+檔案+Slot) | 996 → 2010 (+1014) |
修改後實際列長 ≈ 1998 bytes (平均) → 單頁僅容 4 列 → 10 列需 3 頁 (24 KB)。
若表有 100 萬列：
原佔 ≈ 1000000 × 998 / 8096 ≈ 123,000 頁 ≈ 960 MB。
修改後 ≈ 1000000 × 1998 / 8096 ≈ 247,000 頁 ≈ 1.9 GB。
空間近乎翻倍！
-- 1. 建立新結構表
CREATE TABLE tb_new (id INT IDENTITY(1,1), col CHAR(1000));
-- 2. 資料遷移 (最小記錄模式需簡單復原/大量記錄 + TABLOCK)
INSERT INTO tb_new WITH (TABLOCK) SELECT * FROM tb;
-- 3. 切換 (需排他鎖定窗口)
BEGIN TRANSACTION;
DROP TABLE tb;
EXEC sp_rename 'tb_new', 'tb';
COMMIT;
-- 4. 重建索引/統計資料
ALTER INDEX ALL ON tb REBUILD;
UPDATE STATISTICS tb;
ALTER TABLE ... SWITCH 瞬間切換，零停機。ALTER TABLE ... REBUILD (SQL 2008+) 同樣會重建表，但仍可能保留轉發指標邏輯，建新表遷移最乾淨。04 具體含義？0100DA870004 固定值代表何意？ALTER TABLE ALTER COLUMN 修改固定長度欄位，底層實作為「每列尾端新增欄位」而非就地擴充，導致列長暴增、頁面分裂、轉發指標/回溯指標雙重開銷、空間近乎翻倍。大型表務必採用建新表遷移 + 切換模式。DBCC IND/PAGE 是窺探儲存引擎行為的利器，建議 DBA 掌握基本用法，驗證結構變更真實影響。
