萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 表值參數 (TVP) 完整指南:用戶定義表類型、ADO.NET 調用、替代方案對比

什麼是表值參數 (TVP)?

SQL Server 2008 引入,**單次參數化呼叫傳遞多行資料**給預存程序/動態 SQL 的內建機制。基礎是 **用戶定義表類型 (UDTT)**。

建立與使用流程

1. 建立用戶定義表類型

CREATE TYPE dbo.PersonInfo AS TABLE (
  ID INT PRIMARY KEY,
  Name NVARCHAR(50),
  Age INT
);

2. 預存程序接收 TVP

CREATE PROCEDURE usp_InsertPersons
  @Persons dbo.PersonInfo READONLY  -- 必須 READONLY
AS
BEGIN
  INSERT INTO Person (ID, Name, Age)
  SELECT ID, Name, Age FROM @Persons;
END

3. ADO.NET 調用

var dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int));
dt.Rows.Add(1, "Alice", 30);
dt.Rows.Add(2, "Bob", 25);

using (var cmd = new SqlCommand("usp_InsertPersons", conn))
{
  cmd.CommandType = CommandType.StoredProcedure;
  var p = cmd.Parameters.AddWithValue("@Persons", dt);
  p.SqlDbType = SqlDbType.Structured;
  p.TypeName = "dbo.PersonInfo";  // 必須指定 UDTT 名稱
  cmd.ExecuteNonQuery();
}

支援輸入來源:DataTable、IList、DbDataReader (可流式傳輸 OracleDataReader 等)。

UDTT / TVP 限制清單

限制類別具體限制
定義限制不可作資料表欄位型別、不可作 UDTT 欄位、無 DEFAULT、建立後不可 ALTER、不可作計算欄參數
索引限制僅允許主鍵/唯一約束建立的索引
TVP 參數限制**唯讀**:不可 UPDATE/INSERT/DELETE 參數內容;需修改請先複製至暫表/表變數
函數限制不可作 UDF 參數、不可作 UDF 回傳型別

TVP 出現前的替代方案對比

方案缺點
多個純量參數參數上限 2100、服務端需手動組裝、維護惡夢
分隔字串 / XML需剖析驗證邏輯、型別安全差、大資料效能差
多條 SQL 批次提交服務端仍逐條執行、網路來回、無原子性
BCP / SqlBulkCopy高效但**無法在伺服器端直接處理**,需落地暫表再處理

TVP 核心優勢

  • **強型別**、**編譯期檢查**、IntelliSense 支援
  • **單次往返** 傳遞批量資料,網路開銷極低
  • **集合式操作**,伺服器端高效處理 (INSERT/UPDATE/MERGE)
  • 統計資料自動維護,優化器可產生最佳計畫

最佳實踐

  1. UDTT 定義置於專用 Schema (如 dbo.Types)
  2. 主鍵/唯一索引配合業務鍵,避免重複資料
  3. 大批量 (萬行以上) 配合 SqlBulkCopy 直寫暫表,再用 TVP 處理邏輯
  4. 注意 READONLY 限制,業務邏輯在 SP 內用暫表/表變數中轉
copyright © 萬盛學電腦網 all rights reserved