SQL Server 2005 T-SQL 新功能學習總結：大值資料類型 (MAX) 與 XML 資料類型

一、大值資料類型 (MAX 指定符)

SQL Server 2005 引入 MAX 指定符，擴充 varchar、nvarchar、varbinary 存儲上限至 2^31-1 位元組 (2 GB)，統稱「大值資料類型」。

對應舊類型對照

重要：未來版本將移除 text、ntext、image，新開發請直接使用 MAX 類型，舊系統規劃遷移。

部分更新：.WRITE() 子句

UPDATE 支援 .WRITE(expression, @Offset, @Length) 對大值類型進行部分/完整更新，替代舊版文字指標 (UPDATETEXT)。

CREATE TABLE test1(ids INT, DocumentSummary NVARCHAR(MAX));
INSERT INTO test1 VALUES (1, N'abcdefg');

-- 1. 從位移 4 截斷 (保留前 4 字元)
UPDATE test1 SET DocumentSummary.WRITE(NULL, 4, 0);   -- 結果: 'abcd'

-- 2. 結尾追加
UPDATE test1 SET DocumentSummary.WRITE(N'hi', NULL, 0); -- 結果: 'abcdefghi'

-- 3. 從位移 1 替換到結尾
UPDATE test1 SET DocumentSummary.WRITE(N'hi', 1, NULL); -- 結果: 'ahi'

.WRITE() 關鍵規則

1. @Offset 為零基索引 (0 代表第一字元)。
2. 無法更新 NULL 值欄位，也不能將值設為 NULL (需用一般 SET col = value)。
3. expression = NULL 時忽略 @Length，從 @Offset 截斷至結尾。
4. @Offset = NULL 時在結尾追加 expression，忽略 @Length。
5. @Length = NULL 時刪除從 @Offset 到結尾的所有資料。

其他類型部分更新

非 MAX 類型 (如 varchar(100)) 可用 STUFF、SUBSTRING 等字串函數組合實現。

二、XML 資料類型

• 原生儲存 XML 文件/片段，單一實例上限 2 GB。
• 支援結構描述集合 (Schema Collection) 驗證 (Typed XML) 或不驗證 (Untyped XML)。
• 提供豐富方法：query()、value()、exist()、nodes()、modify() (XQuery/XPath)。
• 可建立 XML 索引 (Primary/Secondary) 提升查詢效能。

遷移建議

1. 新表直接用 varchar(max) 等 MAX 類型。
2. 舊表 text/ntext/image 可用 ALTER TABLE ... ALTER COLUMN col VARCHAR(MAX) 就地轉換 (資料不動)。
3. 應用程式碼中 ADO.NET 參數類型對應 SqlDbType.VarChar + Size = -1 表示 MAX。