您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 表值參數完整教學：建立、傳遞與效能優勢

編輯：關於SqlServer

前言

SQL Server 2008 引入表值參數，允許將整個資料表作為參數傳遞給預存程序或函數，解決舊版本需透過 XML 字串剖析的痛苦，大幅簡化開發並提升效能。

建立使用者定義資料表類型

-- 1. 建立範例資料表
IF OBJECT_ID('dbo.SalesHistory') IS NOT NULL DROP TABLE dbo.SalesHistory;
CREATE TABLE dbo.SalesHistory (
    SaleID    INT IDENTITY(1,1) PRIMARY KEY,
    Product   VARCHAR(10),
    SaleDate  DATETIME,
    SalePrice MONEY
);

-- 2. 建立表值類型（必須先建立類型，才能在參數宣告中使用）
CREATE TYPE dbo.SalesHistoryTableType AS TABLE (
    Product   VARCHAR(10),
    SaleDate  DATETIME,
    SalePrice MONEY
);
GO

建立接收表值參數的預存程序

CREATE PROCEDURE dbo.usp_InsertBigScreenProducts
    @TableVariable dbo.SalesHistoryTableType READONLY  -- 必須指定 READONLY
AS
BEGIN
    INSERT INTO dbo.SalesHistory (Product, SaleDate, SalePrice)
    SELECT Product, SaleDate, SalePrice
    FROM @TableVariable
    WHERE Product = 'BigScreen';
END;
GO

呼叫端：宣告變數、填入資料、執行程序

DECLARE @DataTable dbo.SalesHistoryTableType;
DECLARE @i SMALLINT = 1;

-- 批次產生 1000 筆測試資料
WHILE @i <= 1000
BEGIN
    INSERT INTO @DataTable (Product, SaleDate, SalePrice)
    VALUES ('Computer', DATEADD(MONTH, @i, '1919-03-11'), DATEPART(MS, GETDATE()) + @i + 57);

    INSERT INTO @DataTable (Product, SaleDate, SalePrice)
    VALUES ('BigScreen', DATEADD(MONTH, @i, '1927-03-11'), DATEPART(MS, GETDATE()) + @i + 13);

    INSERT INTO @DataTable (Product, SaleDate, SalePrice)
    VALUES ('PoolTable', DATEADD(MONTH, @i, '1908-03-11'), DATEPART(MS, GETDATE()) + @i + 29);

    SET @i += 1;
END

-- 執行預存程序，傳遞表值參數
EXEC dbo.usp_InsertBigScreenProducts @TableVariable = @DataTable;

關鍵限制與特性

項目	說明
參數方向	僅支援 `INPUT`（必須加 `READONLY`），不支援 `OUTPUT`
資料儲存位置	資料實際存在 `tempdb`，非記憶體，適合大量資料
傳遞機制	參考傳遞（指向 tempdb 物件的指標），無序列化開銷
可在函數使用	支援標量函數與資料表值函數
用戶端支援	ADO.NET `SqlParameter.TypeName` 設定為 UDT 名稱，`Value` 傳 `DataTable`/`IEnumerable<SqlDataRecord>`

效能優勢

減少網路往返：單次呼叫傳遞成批資料，取代多次 INSERT 迴圈。
避免 XML 剖析：舊版常用 XML 字串傳遞，需在 T-SQL 中用 .nodes() 撕解，CPU 成本高。
計畫重用：預存程序計畫可重用，參數化查詢避免編譯風暴。

ADO.NET 客戶端範例

DataTable dt = new DataTable();
dt.Columns.Add("Product", typeof(string));
dt.Columns.Add("SaleDate", typeof(DateTime));
dt.Columns.Add("SalePrice", typeof(decimal));
// ... 填入資料 ...

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("usp_InsertBigScreenProducts", conn)) {
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvp = cmd.Parameters.AddWithValue("@TableVariable", dt);
    tvp.SqlDbType = SqlDbType.Structured;
    tvp.TypeName = "dbo.SalesHistoryTableType";  // 必須指定 UDT 名稱
    conn.Open();
    cmd.ExecuteNonQuery();
}