萬盛學電腦網

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

SQL Server 使用者定義資料表類型 (Table-Valued Parameters) 實戰教學

什麼是使用者定義資料表類型?

自 SQL Server 2008 起支援。它是一種使用者自訂的資料表結構型別,可用於:

  • 預存程序/函數的資料表值參數 (TVP):一次傳遞多列資料給伺服器。
  • 批次/預存程序內部的資料表變數:替代臨時表,範圍限定於批次。

建立範例:聯絡人資料表與對應類型

1. 目標資料表


CREATE TABLE dbo.Contact (
    ContactID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(80) NOT NULL,
    LastName NVARCHAR(80) NOT NULL,
    Email NVARCHAR(80) NOT NULL,
    Phone VARCHAR(25) NULL,
    Created DATETIME NOT NULL DEFAULT GETDATE()
);

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


CREATE TYPE dbo.ContactStruct AS TABLE (
    ContactID UNIQUEIDENTIFIER NOT NULL,
    FirstName NVARCHAR(80) NOT NULL,
    LastName NVARCHAR(80) NOT NULL,
    Email NVARCHAR(80) NOT NULL,
    Phone VARCHAR(25) NOT NULL,
    PRIMARY KEY CLUSTERED (ContactID ASC)
    WITH (IGNORE_DUP_KEY = OFF)
);
SSMS 中的使用者定義資料表類型

使用 TVP 的預存程序:批次插入聯絡人


CREATE PROCEDURE dbo.InsertContacts
    @contacts dbo.ContactStruct READONLY  -- 必須加 READONLY
AS
BEGIN
    INSERT INTO dbo.Contact (ContactID, FirstName, LastName, Email, Phone)
    SELECT ContactID, FirstName, LastName, Email, Phone FROM @contacts;
    RETURN 0;
END

用戶端呼叫範例 (C# ADO.NET)


var dt = new DataTable();
dt.Columns.Add("ContactID", typeof(Guid));
dt.Columns.Add("FirstName", typeof(string));
dt.Columns.Add("LastName", typeof(string));
dt.Columns.Add("Email", typeof(string));
dt.Columns.Add("Phone", typeof(string));

// 填入多筆資料
for (int i = 0; i < 100; i++)
    dt.Rows.Add(Guid.NewGuid(), "First" + i, "Last" + i, $"user{i}@test.com", "0912345678");

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("dbo.InsertContacts", conn) { CommandType = CommandType.StoredProcedure })
{
    var p = cmd.Parameters.AddWithValue("@contacts", dt);
    p.SqlDbType = SqlDbType.Structured;
    p.TypeName = "dbo.ContactStruct";
    conn.Open();
    cmd.ExecuteNonQuery();
}

限制與注意事項 (重要!)

限制說明
不可 ALTER TYPE建立後無法修改結構 (增/刪/改欄位),需刪除重建 (DROP TYPE)。相依物件 (SP, Function) 必須先移除。
計算欄位不可呼叫 UDF型別定義中的計算欄位不得參考使用者定義函數。
非叢集索引限制只能建立叢集索引 (透過 PRIMARY KEY / UNIQUE 約束),無法直接建立非叢集索引。
不可作為資料表欄位不能用於 CREATE TABLE ... col dbo.ContactStruct,也不能嵌套在其他結構化型別中。
TVP 唯讀參數必須標示 READONLY,不支援 OUTPUT 方向,無法從 SP 回傳資料表值參數內容。
統計資訊TVP 沒有統計資訊,查詢最佳化器假設固定基數 (舊版 1 行,新版較智慧),複雜查詢可能產生次優計畫。

適用場景

  • 批次匯入/更新 (替代迴圈呼叫 SP 或大量 INSERT 陳述式)。
  • 應用層已有 DataTable/DataSet/List,欲一次送入資料庫。
  • 替代 XML/JSON 傳遞結構化資料 (效能更好、型別安全)。

總結

使用者定義資料表類型 + TVP 是 SQL Server 批次資料傳遞的原生高效方案。雖有「不可 ALTER」、「唯讀」、「無統計資訊」等限制,但在批次寫入、多列參數化查詢場景極具優勢。善用它可大幅減少網路往返與伺服器端解析開銷。

copyright © 萬盛學電腦網 all rights reserved