自 SQL Server 2008 起支援。它是一種使用者自訂的資料表結構型別,可用於:
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()
);
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)
);
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
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 行,新版較智慧),複雜查詢可能產生次優計畫。 |
使用者定義資料表類型 + TVP 是 SQL Server 批次資料傳遞的原生高效方案。雖有「不可 ALTER」、「唯讀」、「無統計資訊」等限制,但在批次寫入、多列參數化查詢場景極具優勢。善用它可大幅減少網路往返與伺服器端解析開銷。