SQL Server 使用者定義表類型 (UDTT) 完整入門與限制說明

概念

使用者定義表類型 (User-Defined Table Type, UDTT) 為預先定義的表結構，可作為：

• 預存程序/函數的 表值參數 (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. 建立 UDTT

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 中的使用者定義表類型

3. 使用 TVP 的預存程序

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

呼叫方式 (C# 範例)

var dt = new DataTable();
dt.Columns.Add("ContactID", typeof(Guid));
dt.Columns.Add("FirstName", typeof(string));
// ... 填入資料

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

四大限制 (必讀)

1. 建立後不可修改：需刪除重建 (DROP TYPE ...; CREATE TYPE ...)。
2. 計算欄位不可呼叫使用者函數。
3. 無法建立非叢集索引，除非由 PRIMARY KEY/UNIQUE 約束產生。
4. 不可作為資料表欄位或結構化 UDT 欄位，僅限參數/變數使用。

適用場景

• 批次大量插入/更新 (替代 XML/字串拆解)
• 應用層與資料庫交換結構化集合資料
• 減少 Round-trip，提升高併發寫入效能