SQL Server 數據類型詳解：CHAR/VARCHAR/NCHAR/NVARCHAR 存儲差異與字串函數陷阱

前言

數據類型是資料庫設計的基石。本文深入剖析 SQL Server 字符數據類型的存儲機制差異，並揭示 LEN/DATALENGTH、CHARINDEX/PATINDEX 等常用函數的隱藏行為差異。

字符數據類型核心差異

儲存機制對比

字面值表示差異

• 常規：'Hello World'
• Unicode：N'Hello World' （前綴 N）

實戰驗證：中英文混合存儲行為

CREATE TABLE UnicodeType (
    firstname VARCHAR(5) NOT NULL,
    lastname NVARCHAR(5) NOT NULL
)

測試 1：VARCHAR(5) 存 5 個中文字

INSERT dbo.UnicodeType (firstname, lastname) VALUES ('達得到讓人', N'達得到讓人')

⚠️ 截斷警告：VARCHAR(5) 僅 5 字節，5 個中文需 10 字節 → 截斷。

測試 2：VARCHAR(5) 存 2 中文 + 1 數字（4+1=5 字節）

INSERT dbo.UnicodeType (firstname, lastname) VALUES ('達得1', N'達得到讓人')

✅ 成功：2中文(4字節)+1數字(1字節)=5字節，剛好放入。

測試 3：NVARCHAR(5) 存 4 中文 + 2 英文

INSERT dbo.UnicodeType (firstname, lastname) VALUES ('達得1', N'達得到讓ab')

❌ 失敗：NVARCHAR 中英文均佔 2 字節 → 4中文(8)+2英文(4)=12 字節 > 10 字節上限。

INSERT dbo.UnicodeType (firstname, lastname) VALUES ('達得1', N'達得到讓b') -- 4中文+1英文=10字節 ✅

核心結論

常規類型： 中文 2 字節、英文 1 字節
Unicode 類型： 中文 2 字節、英文 同樣 2 字節

這打破了「Unicode 英文佔 1 字節」的常見誤區。

關鍵字串函數行為差異

LEN vs DATALENGTH

CREATE TABLE StringFun (
    firststr VARCHAR(MAX) NOT NULL,
    secondstr TEXT NOT NULL
)
INSERT dbo.StringFun VALUES (
    '我是JeffckyWang，我來自於博客園，專注於.NET技術',
    '我是JeffckyWang，我來自於博客園，專注於.NET技術'
)

LEN： 回傳字符數（邏輯長度），忽略尾隨空格，不支援 TEXT 類型報錯。

DATALENGTH： 回傳儲存字節數（物理長度），包含尾隨空格，支援所有類型含 TEXT。

NULL 處理： 兩者對 NULL 均回傳 NULL，而非 0。

尾隨空格差異：

SELECT LEN('JeffckyWang ')       -- 11（去尾空格）
SELECT DATALENGTH('JeffckyWang ') -- 12（含尾空格）

CHARINDEX vs PATINDEX

兩者皆回傳匹配起始位置，但通配符要求不同：

-- CHARINDEX：無需通配符
SELECT CHARINDEX('Worn', DocumentSummary) FROM Production.Document WHERE ChangeNumber = 55

-- PATINDEX：必須 加通配符 %Worn%
SELECT PATINDEX('%Worn%', DocumentSummary) FROM Production.Document WHERE ChangeNumber = 55

→

總結

• 選擇 CHAR/VARCHAR 還是 NCHAR/NVARCHAR 取決於國際化需求與儲存成本權衡。
• LEN 適合邏輯長度檢查，DATALENGTH 適合儲存空間計算與 TEXT 類型。
• PATINDEX 支援模式匹配但強制通配符，CHARINDEX 適合精確子串定位。
• 避免在 VARCHAR 存中文，改用 NVARCHAR 杜絕截斷風險。