萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server NULL 值完全指南:正確查詢、空間占用與 ANSI_NULLS 陷阱

NULL 的本質

「NULL 表示值未知。NULL 不等於空字串、不等於零、不等於另一個 NULL。任何與 NULL 比較結果皆為 UNKNOWN。」—— MSDN

測試資料準備

CREATE TABLE Test_NULL (
    num INT NOT NULL PRIMARY KEY,
    fname NVARCHAR(50) NULL,
    lname NVARCHAR(50) NULL
);

INSERT INTO Test_NULL (num,fname,lname) VALUES (1,'Tom','Jane');
INSERT INTO Test_NULL (num,fname,lname) 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

正確查詢 NULL 的方式

目的正確語法錯誤示範
尋找 NULLWHERE lname IS NULLWHERE lname = NULL
排除 NULLWHERE lname IS NOT NULLWHERE lname <> NULL
轉換 NULLISNULL(lname, 'N/A') 或 COALESCE(lname, 'N/A')CASE WHEN lname = NULL THEN ...

驗證 NULL ≠ NULL

SELECT * FROM Test_NULL t1
LEFT JOIN Test_NULL t2 ON t1.num = t2.num
WHERE t1.lname = t2.lname;
-- 僅回傳 2 筆(Tom/Jane 與 Dave/''),NULL 不匹配 NULL

ANSI_NULLS 設定影響

  • 預設 ON: 符合 ANSI 標準,= NULL 恒為 UNKNOWN
  • 設為 OFF: 允許 = NULL 比較,不建議使用,未來版本將移除
SET ANSI_NULLS OFF;
SELECT * FROM Test_NULL WHERE lname = NULL; -- 可查出 NULL,但屬非標準行為
SET ANSI_NULLS ON; -- 恢復預設

NULL 的空間占用真相

常見迷思:「NULL 在可變長度欄位不佔空間,固定長度才佔空間」

事實: 在非 Sparse Column 的資料表中,每個欄位無論固定/可變長度,NULL 皆佔用 1 bit 的 NULL Bitmap Mask。

  • 固定長度欄位(int, datetime 等):預留完整長度 + 1 bit bitmap
  • 可變長度欄位(varchar, nvarchar):2 位元組偏移量陣列項目 + 1 bit bitmap(實際資料區不佔空間)

Sparse Column 例外: 啟用 SPARSE 後,NULL 真正 0 空間,但非 NULL 值會多佔 4 位元組額外開銷。

插入與更新 NULL

-- 明確插入 NULL
INSERT INTO Test_NULL (num,fname,lname) VALUES (5,'Serena',NULL);

-- 更新為 NULL
UPDATE Test_NULL SET fname = NULL WHERE num = 2;

結語

NULL 是「未知」的標記,而非「空值」。永遠使用 IS NULL / IS NOT NULL 判斷,善用 ISNULL/COALESCE 處理顯示,理解 NULL Bitmap 對儲存體的固定開銷,避免 ANSI_NULLS OFF 導致的非標準行為,才能寫出健壯的 T-SQL 程式碼。

copyright © 萬盛學電腦網 all rights reserved