SQL Server 2005 MAX 資料類型：突破 8000 位元組限制的大物件儲存方案

背景問題

SQL Server 2000 中 VARCHAR 限制 8000 位元組，大型文件需使用 TEXT/IMAGE，但這兩類型不支援大多數字串函數（REPLACE、SUBSTRING、CHARINDEX 等），更新子字串需使用晦澀的 READTEXT/WRITETEXT，開發與維護極度不便。

MAX 資料類型介紹

SQL Server 2005 引入 VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)，最大儲存 2 GB，完全相容所有內建字串/二進位函數。

宣告與使用範例

DECLARE @BigString VARCHAR(MAX);
SET @BigString = 'abc';
-- 可直接傳遞給 REPLACE, SUBSTRING, LEN 等函數
SELECT LEN(@BigString);  -- 正常運作

重要行為差異與陷阱

1. 隱含轉換不會自動升級為 MAX

DECLARE @S1 VARCHAR(4001), @S2 VARCHAR(4001);
SET @S1 = REPLICATE('1', 4001);
SET @S2 = REPLICATE('2', 4001);
SELECT LEN(@S1 + @S2);  -- 結果 8000，非 8002，因兩者皆為指定長度 VARCHAR，截斷於 8000

修正：任一運算元轉為 MAX 即可：

SELECT LEN(CONVERT(VARCHAR(MAX), @S1) + @S2);  -- 結果 8002

2. 字串字面值預設為指定長度 VARCHAR

SELECT LEN(REPLICATE('1', 8002));  -- 結果 8000
SELECT LEN(REPLICATE(CONVERT(VARCHAR(MAX), '1'), 8002));  -- 結果 8002

資料表欄位定義與行溢出行為

CREATE TABLE BigStrings (BigString VARCHAR(MAX));

• 行內儲存：若該列資料加上其他欄位總計 < 8060 位元組，資料存放於資料頁內（同一般 VARCHAR）。
• 行外溢出：超過 8060 位元組時，MAX 欄位資料移至行外儲存（類似 TEXT/IMAGE），資料列保留 16 位元組指標。
• 預設行為：混合模式——小資料行內、大資料行外，兼顧效能與彈性。

強制行外儲存（模擬 TEXT/IMAGE 行為）

EXEC sp_tableoption 'BigStrings', 'large value types out of row', '1';

設計建議

• 不可濫用 MAX：所有欄位皆用 VARCHAR(MAX) 會導致查詢最佳化器無法估算資料大小，嚴重影響執行計畫品質，且前端介面無法決定適當輸入長度。
• 保留合理長度限制：如 VARCHAR(100) 代表姓名、VARCHAR(200) 代表地址，兼具資料完整性與最佳化資訊。
• 僅於真正需要 > 8000 位元組時使用 MAX：文件內容、長篇記錄、二進位大物件等。

結語

MAX 類型為大物件儲存帶來革命性簡化，開發人員不再需在功能與效能間妥協。善用 MAX 類型並避免過度使用，可構建高效能、易維護的資料庫應用。