SQL Server 圖片/大型文字儲存實戰：WRITETEXT、READTEXT、UPDATETEXT 完整操作指南

早期 SQL Server (2000/2005) 常用 TEXT/NTEXT/IMAGE 資料類型儲存大型物件 (LOB)，操作需透過 WRITETEXT、READTEXT、UPDATETEXT 三大指令。本文整理完整語法、參數與實戰範例。注意：SQL Server 2005+ 建議改用 VARCHAR(MAX)/NVARCHAR(MAX)/VARBINARY(MAX) 搭配標準 DML 操作。

核心函數速查

• TEXTPTR(column)：取得 LOB 欄位指標 (16 位元組二進位)。
• TEXTVALID('table.column', ptr)：驗證指標有效性。
• @@TEXTSIZE / SET TEXTSIZE n：控制單次讀寫大小 (預設 4096 位元組)。
• PATINDEX('%pattern%', column)：搜尋模式位置。
• DATALENGTH(column)：實際資料長度。

1. 寫入：WRITETEXT

BEGIN TRAN
DECLARE @ptr VARBINARY(16)
SELECT @ptr = TEXTPTR(pr_info) FROM Pub_Info (UPDLOCK) WHERE pub_id = '9999'
IF @ptr IS NOT NULL
    WRITETEXT Pub_Info.pr_info @ptr WITH LOG '新資料內容'
COMMIT TRAN

• WITH LOG：記錄至交易日誌 (可復原)；預設最小化日誌。
• 必須在交易中配合 UPDLOCK 取得指標，避免並發衝突。

2. 讀取：READTEXT (分塊讀取)

BEGIN TRAN
DECLARE @ptr VARBINARY(16), @total INT, @read INT, @offset INT = 0
SET TEXTSIZE 100  -- 每次讀 100 位元組
SELECT @ptr = TEXTPTR(pr_info), @total = DATALENGTH(pr_info)
FROM Pub_Info WHERE pub_id = '1622'

IF @ptr IS NOT NULL AND @total > 0
BEGIN
    WHILE @offset < @total
    BEGIN
        SET @read = CASE WHEN @offset + @@TEXTSIZE < @total THEN @@TEXTSIZE ELSE @total - @offset END
        READTEXT Pub_Info.pr_info @ptr @offset @read HOLDLOCK
        IF @@ERROR <> 0 BREAK
        SET @offset = @offset + @read
    END
END
COMMIT TRAN

3. 更新：UPDATETEXT (取代 WRITETEXT)

UPDATETEXT Table_Name.Column_Name @ptr @offset @delete_len [WITH LOG] [@new_data | Table_Name.Src_Column @src_ptr]

範例：完整取代

DECLARE @ptr VARBINARY(16)
BEGIN TRAN
SELECT @ptr = TEXTPTR(pr_info) FROM Pub_Info (UPDLOCK) WHERE pub_id = '9999'
UPDATETEXT Pub_Info.pr_info @ptr 0 NULL WITH LOG '全新內容'
COMMIT TRAN

範例：追加

UPDATETEXT Pub_Info.pr_info @ptr NULL 0 WITH LOG '追加內容'

範例：部分取代 (第 10 字元起 5 字元換成 'ABC')

UPDATETEXT Pub_Info.pr_info @ptr 10 5 WITH LOG 'ABC'

現代化建議

SQL Server 2005 起支援 MAX 指定符：

CREATE TABLE Docs (Id INT PRIMARY KEY, Content VARBINARY(MAX))
INSERT INTO Docs VALUES (1, 0x...)  -- 標準 INSERT
UPDATE Docs SET Content = 0x... WHERE Id = 1  -- 標準 UPDATE
SELECT Content FROM Docs WHERE Id = 1       -- 標準 SELECT

• 支援完整 DML、索引、壓縮、Filestream 整合。
• 新專案請勿再使用 TEXT/NTEXT/IMAGE。

總結

掌握 TEXTPTR + WRITETEXT/READTEXT/UPDATETEXT 組合，可維護舊系統 LOB 資料。新開發請全面轉向 MAX 類型，享受標準 SQL 操作與更優效能。