SQL Server NULL 深度解析：正確查詢、空間佔用、ANSI_NULLS 陷阱

什麼是 NULL？

NULL 代表「未知值」，非空字串、非零、非空集合。
關鍵特性：NULL = NULL 結果為 UNKNOWN（非 TRUE），任何與 NULL 比較（=, <>, >, <）皆回傳 UNKNOWN。

測試環境

CREATE TABLE Test_NULL (
    num INT NOT NULL PRIMARY KEY,
    fname NVARCHAR(50) NULL,
    lname NVARCHAR(50) NULL
);
INSERT INTO Test_NULL VALUES (1, 'Tom', 'Jane');
INSERT INTO Test_NULL VALUES (2, 'Dave', '');      -- 空字串
INSERT INTO Test_NULL (num, fname) VALUES (3, 'Aaron'); -- lname = NULL
INSERT INTO Test_NULL (num, fname) VALUES (4, 'Betty'); -- lname = NULL

常見誤區與正確查法

ANSI_NULLS 影響

預設 SET ANSI_NULLS ON（SQL Server 預設），上述規則生效。
若 SET ANSI_NULLS OFF，則 = NULL 可匹配 NULL 值，但強烈不建議關閉，因：

• 違反 ANSI 標準，移植性差。
• 影響索引檢視表、計算欄位、篩選索引建立（需 ON）。
• 未來版本將移除 OFF 選項。

NULL 空間佔用真相

許多人以為「可變長欄位 NULL 不佔空間，固定長度佔空間」。實際上兩者皆佔用空間：

• 每列資料列在 NULL Bitmap Mask 中佔 1 bit（標示是否為 NULL）。
• 固定長度欄位（如 INT）額外佔用定義長度位元組（4 bytes）。
• 可變長度欄位（VARCHAR、NVARCHAR）不佔用資料區位元組，但仍佔用 NULL Bitmap 1 bit + 位移陣列 2 bytes。
• SPARSE COLUMN 例外：大量 NULL 時可節省空間，但非 NULL 值會多佔 4 bytes。

插入/更新 NULL 範例

INSERT INTO Test_NULL (num, fname, lname) VALUES (5, 'Serena', NULL);
UPDATE Test_NULL SET fname = NULL WHERE num = 2;

最佳實踐

1. 永遠使用 IS NULL / IS NOT NULL 判斷。
2. 業務上「不適用」優先用 NOT NULL + 預設值，避免三值邏輯陷阱。
3. 可變長欄位允許 NULL 可節省資料區空間，但仍有元資料開銷。
4. 索引鍵欄位盡量 NOT NULL，提升統計資訊準確度與查詢效能。