SQL Server 2008 表值參數 (TVP) 實戰：告別 XML/Split 函數的批量傳遞新紀元

歷史痛點：批量傳遞的三座大山

1. XML 方案：組裝/解析 XML 字串，CPU 密集，代碼冗長。
2. T-SQL Split 函數：字串分割效能差，無法利用索引，維護成本高。
3. CLR Split：部署複雜，需啟用 CLR，版本升級易斷裂。

SQL Server 2008 破局特性：使用者定義表類型 (UDTT) + 表值參數 (TVP)

將「表結構」定義為強型別，直接作為參數傳遞給預存程序/函數，享受：

• 型別安全（編譯期檢查欄位名、型別、約束）。
• 零序列化開銷（內存表直接傳遞）。
• 可建立索引/主鍵/唯一約束（如範例 PRIMARY KEY (col1)）。
• 與 MERGE、INSERT...SELECT 無縫整合。

完整建立腳本

USE Test;
GO

-- 1. 建立使用者定義表類型
CREATE TYPE dbo.MyType AS TABLE
(
    col1 INT NOT NULL,
    col2 VARCHAR(20) NULL,
    col3 DATETIME NULL,
    PRIMARY KEY (col1)  -- 支援索引與約束！
);
GO

-- 2. 使用 TVP 的預存程序
CREATE PROCEDURE usp_BatchUpsertProducts
    @Products dbo.MyType READONLY  -- 必須加 READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- 範例：UPSERT 邏輯
    MERGE INTO dbo.Products AS Target
    USING @Products AS Source
    ON Target.ProductID = Source.col1
    WHEN MATCHED THEN
        UPDATE SET Name = Source.col2, ModifiedDate = Source.col3
    WHEN NOT MATCHED THEN
        INSERT (ProductID, Name, ModifiedDate)
        VALUES (Source.col1, Source.col2, Source.col3);
END
GO

-- 3. 客戶端呼叫範例 (C#)
/*
DataTable tvp = new DataTable();
tvp.Columns.Add("col1", typeof(int));
tvp.Columns.Add("col2", typeof(string));
tvp.Columns.Add("col3", typeof(DateTime));
// 填充資料...
using (SqlCommand cmd = new SqlCommand("usp_BatchUpsertProducts", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    var param = cmd.Parameters.AddWithValue("@Products", tvp);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "dbo.MyType";
    cmd.ExecuteNonQuery();
}
*/

圖示說明

進階實戰模式

模式 1：大量刪除

CREATE TYPE dbo.IdList AS TABLE (Id INT PRIMARY KEY);
GO
CREATE PROCEDURE usp_BatchDelete @Ids dbo.IdList READONLY
AS
DELETE FROM TargetTable WHERE Id IN (SELECT Id FROM @Ids);

模式 2：多 TVP 協同

CREATE PROCEDURE usp_SyncOrderHeaderDetail
    @Headers dbo.OrderHeaderType READONLY,
    @Details dbo.OrderDetailType READONLY
AS
BEGIN
    -- Header UPSERT
    MERGE ... USING @Headers ...;
    -- Detail UPSERT (FK 關聯 Header)
    MERGE ... USING @Details ...;
END

模式 3：TVP + 分割表

將 TVP 資料直接 INSERT 至分割表的 Staging 分割區，再 SWITCH 進主分割區，實現極速載入。

效能基準對比（百萬筆級）

遷移清單

1. 盤點所有使用 XML/Split 的預存程序與應用程式呼叫點。
2. 為每個參數結構建立對應 UDTT（命名建議 Type__Param）。
3. 改寫預存程序簽名與邏輯，利用 MERGE 簡化 UPSERT。
4. 更新應用程式：ADO.NET 使用 SqlDbType.Structured + DataTable/IEnumerable。
5. 壓力測試驗證吞吐量與延遲達標。

總結

TVP 是 SQL Server 2008 最具生產力提升的 T-SQL 特性之一。它將「表」作為一等公民參數，徹底消除批量傳遞的序列化/反序列化、字串拼接、動態 SQL 等反模式。配合 MERGE、分割表、Columnstore 索引，構建出高效、可維護、型別安全的資料搬運管線。