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

SQL Server 2008 表值參數完整實驗：從多參數、暫存表到 Table-Valued Parameters 的演進

編輯：關於SqlServer

實驗目標

比較三種向預存程序傳遞多列資料的方式：多參數呼叫、用戶端暫存表、表值參數 (TVP)，體驗 TVP 的強型別、唯讀、自動清除、減少網路往返等優勢。

環境準備

虛擬機：SQL Server 2008 (本機)、Windows 驗證、SQL2008DEMO 資料庫
工具：SQL Server Management Studio

方案一：多參數多次呼叫（基準）

CREATE PROCEDURE NewEmployeeMS (@EmpID INT, @EmpName NVARCHAR(100), @EmpEmail NVARCHAR(100))
AS INSERT INTO dbo.Employee VALUES (@EmpID, @EmpName, @EmpEmail);

EXEC NewEmployeeMS 1, 'John McLean', 'JohnMcLean@contoso.com';
EXEC NewEmployeeMS 2, 'Bob Smith', 'BobSmith@contoso.com';
EXEC NewEmployeeMS 3, 'Ted Connery', 'TedConnery@contoso.com';

缺點：多次網路往返、預存程序重複執行、效率低。

方案二：用戶端暫存表

CREATE TABLE #EmployeeTempTable (EmpID INT, EmpName NVARCHAR(100), EmpEmail NVARCHAR(100));
INSERT INTO #EmployeeTempTable VALUES (1,'John McLean','JohnMcLean@contoso.com'), ...;

CREATE PROCEDURE NewEmployeeTempTable
AS INSERT INTO dbo.Employee SELECT * FROM #EmployeeTempTable;

EXEC NewEmployeeTempTable;
DROP TABLE #EmployeeTempTable;

缺點：暫存表在 tempdb 產生 I/O、易造成鎖定阻塞、需手動清理、導致預存程序重新編譯。

方案三：表值參數 (TVP) —— 推薦

步驟 1：建立使用者定義表類型

CREATE TYPE EmployeeTableType AS TABLE (
    EmpID INT, EmpName NVARCHAR(100), EmpEmail NVARCHAR(100)
);

步驟 2：建立接收 TVP 的預存程序

CREATE PROCEDURE NewEmployee (@EmployeeDetails EmployeeTableType READONLY)
AS INSERT INTO dbo.Employee SELECT * FROM @EmployeeDetails;

規則：TVP 必須標記 READONLY，程序內不可對其執行 DML。

步驟 3：用戶端宣告變數、填資料、單次呼叫

DECLARE @NewEmployees EmployeeTableType;
INSERT INTO @NewEmployees VALUES (1,'John McLean','JohnMcLean@contoso.com'),
                                 (2,'Bob Smith','BobSmith@contoso.com'),
                                 (3,'Ted Connery','TedConnery@contoso.com');
EXECUTE NewEmployee @NewEmployees;

程序結束後 TVP 變數自動銷毀，無殘留物件。