SQL Server 2005 VARCHAR(MAX) 大數據類型深度解析：徹底取代 TEXT/NTEXT/IMAGE

歷史痛點：TEXT/NTEXT/IMAGE 的程式設計惡夢

SQL Server 2000 時代，超過 8000 位元組的資料只能用 `TEXT`/`NTEXT`/`IMAGE`。但它們是**二等公民**：

• 無法使用標準字串函數 (`REPLACE`, `SUBSTRING`, `CHARINDEX`, `LEN` 等)。
• 無法宣告區域變數 (`DECLARE @v TEXT` 錯誤)。
• 更新需晦澀的 `READTEXT`/`WRITETEXT`/`UPDATETEXT`，邏輯複雜、效能差、易出錯。
• 無法線上索引、無法參與許多查詢優化。

案例：市場部要將數萬份文件中標語 `"It's MegaWare's Way or the Highway!"` 替換為 `"MegaWare: It's really cool!"`，`UPDATE Docs SET Content = REPLACE(Content, ...)` 直接報錯 `"Argument 1 of REPLACE is invalid for TEXT data type"`。DBA 整夜鑽研 `UPDATETEXT` 修補，極度痛苦。

VARCHAR(MAX) / NVARCHAR(MAX) / VARBINARY(MAX) — 統一大物件模型

核心優勢

• 完整相容所有字串/二進位函數：`REPLACE`, `SUBSTRING`, `LEN`, `+`, `STUFF`, `CHARINDEX`, `PATINDEX` 等原生支援。
• 支援區域變數與參數：`DECLARE @BigStr VARCHAR(MAX); SET @BigStr = REPLICATE(CONVERT(VARCHAR(MAX), 'A'), 1000000);`
• 儲存上限 2 GB (同 `TEXT`)，但行為像 `VARCHAR`。
• 智慧型行溢出儲存：資料 ≤ 8000 位元組且行總長 ≤ 8060 時**行內儲存** (高效存取)；超過則**行外儲存** (類 `TEXT`)，自動管理，無感知。
• 可建立全文索引、XML 索引、計算欄位索引。

定義語法

-- 變數
DECLARE @BigText VARCHAR(MAX);
DECLARE @BigUnicode NVARCHAR(MAX);
DECLARE @BigBinary VARBINARY(MAX);

-- 資料表欄位
CREATE TABLE Documents (
    DocID INT PRIMARY KEY,
    Content VARCHAR(MAX),        -- 取代 TEXT
    UnicodeContent NVARCHAR(MAX), -- 取代 NTEXT
    BinaryData VARBINARY(MAX)     -- 取代 IMAGE
);

關鍵行為陷阱與最佳實踐

陷阱 1：隱含型別截斷 — 字串字面值預設為 VARCHAR(8000)

-- 錯誤：REPLICATE 參數 'A' 推斷為 VARCHAR(1) → VARCHAR(8000) 上限 → 結果截斷 8000
SELECT LEN(REPLICATE('A', 10000)); -- 返回 8000！

-- 正確：強制至少一個運算元為 MAX 型別
SELECT LEN(REPLICATE(CONVERT(VARCHAR(MAX), 'A'), 10000)); -- 返回 10000
SELECT LEN(REPLICATE(CAST('A' AS VARCHAR(MAX)), 10000));  -- 同效果

鐵律： 任何可能超過 8000 字元的字串運算，**至少一個運算元必須顯式 `CAST/CONVERT` 為 `VARCHAR(MAX)`**。

陷阱 2：固定長度變數連接不會自動升級

DECLARE @S1 VARCHAR(4001) = REPLICATE(CAST('1' AS VARCHAR(MAX)), 4001);
DECLARE @S2 VARCHAR(4001) = REPLICATE(CAST('2' AS VARCHAR(MAX)), 4001);
SELECT LEN(@S1 + @S2); -- 返回 8000！因為 @S1/@S2 宣告為 VARCHAR(4001)，優先級低於 MAX

-- 修正：宣告為 MAX 或連接時轉換
DECLARE @S1 VARCHAR(MAX) = ...; -- 或
SELECT LEN(CONVERT(VARCHAR(MAX), @S1) + @S2); -- 返回 8002

行溢出儲存控制：`sp_tableoption 'large value types out of row'`

-- 預設：小資料行內，大資料行外 (自動)
-- 強制所有 MAX 欄位行外儲存 (行為同 TEXT/IMAGE)：
EXEC sp_tableoption 'Documents', 'large value types out of row', '1';

-- 適用場景：表極窄但 MAX 欄位極大且頻繁更新，行外儲存減少頁分裂、避免移動大物件。
-- 一般情況**保持預設 (0/關閉)** 以享受行內儲存的高效存取。

反模式警示：濫用 MAX 當作「免設計」藉口

-- 錯誤示範：所有欄位通用 MAX
CREATE TABLE BadDesign (
    Name VARCHAR(MAX),
    Address VARCHAR(MAX),
    City VARCHAR(MAX),
    ZipCode VARCHAR(MAX)
);

-- 後果：
-- 1. 資料完整性喪失：無長度約束，髒資料風險。
-- 2. UI 設計無依據：輸入框寬度、驗證規則無從制定。
-- 3. 優化器無法估算行大小/選擇性 → 差勁執行計畫 (Hash Join 取代 Nested Loops、記憶體授予失誤)。
-- 4. 索引鍵長度限制 (900 bytes) 仍適用，MAX 欄位無法建索引 (需計算欄位/檢視表)。

原則： **業務語意決定長度**。`VARCHAR(50)` 代表姓名、`VARCHAR(200)` 代表地址、`VARCHAR(MAX)` 專留給真正不可預測長度的大物件 (文件內容、日誌、JSON/XML 文件)。

遷移指南：從 TEXT/NTEXT/IMAGE 升級

1. 評估相依物件：預存程序、函數、檢視表、觸發器、應用程式代碼中使用 `READTEXT`/`WRITETEXT`/`UPDATETEXT`、`TEXTPTR`、`TEXTVALID` 的邏輯。
2. 修改表結構：`ALTER TABLE Docs ALTER COLUMN Content VARCHAR(MAX);` (資料自動遷移，元資料更新瞬時完成)。
3. 重寫程式碼：替換為標準 `UPDATE ... SET Col = REPLACE(Col, ...)`、`SUBSTRING`、`+` 等語法。
4. 測試驗證：功能、效能 (大物件讀寫)、備份/還原、複寫/鏡像/CDC 相容性。
5. 清理：移除舊版相容性層代碼。

總結

`VARCHAR(MAX)` 系列是 SQL Server 2005 最具實用價值的開發者特性之一，**徹底消滅了大物件程式設計的痛苦**，將大物件納入標準關聯模型與函數體系。正確使用鐵律：**業務定長度、MAX 留大物件、運算必轉型、避免濫用**。升級既有 `TEXT` 欄位，將獲得巨大的開發效能與維護性紅利。