使用 Table-Valued 參數（資料庫引擎）

資料表值參數是藉由使用使用者定義的資料表類型來進行宣告。您可以使用資料表值參數，將多個資料列傳送到 Transact-SQL 陳述式或常式 (如預存程序或函式)，而不需要建立暫存資料表或許多參數。

資料表值參數就像是 OLE DB 和 ODBC 中的參數陣列，但是提供了更多的彈性，而且與 Transact-SQL 更緊密整合在一起。資料表值參數也會因為能夠參與以集合為基礎的作業而獲益。

Transact-SQL 會藉傳址方式將資料表值參數傳遞給常式，以免產生輸入資料的複本。您可以使用資料表值參數來建立及執行 Transact-SQL 常式，然後從 Transact-SQL 程式碼 (任何 Managed 語言中的 Managed 和原生用戶端) 呼叫這些常式。

本主題內容：

福利

限制

Table-Valued 參數與 BULK INSERT 作業

範例

優點

資料表值參數的範圍為預存程序、函式或動態 Transact-SQL 文字，與其他參數一模一樣。同樣地，資料表類型之變數的範圍與使用 DECLARE 陳述式建立的其他任何區域變數一樣。您可以在動態 Transact-SQL 陳述式內宣告資料表值變數，並將這些變數當做資料表值參數傳遞給預存程序和函式。

資料表值參數提供更大的彈性，而且在某些情況下，其效能優於暫存資料表或是傳遞參數清單的其他方法。資料表值參數提供下列好處：

不需要從用戶端鎖定初始資料母體。
提供簡單的程式設計模型。
可讓您將複雜的商務邏輯併入單一常式內。
減少與伺服器之間的往返次數。
可以有不同基數度的資料表結構。
強類型化。
可讓用戶端指定排序次序和唯一索引鍵。
在預存程序中使用時，會像暫存資料表一樣被快取。從 SQL Server 2012 開始，數據表值參數也會針對參數化查詢進行快取。

限制

資料表值參數有下列限制：

SQL Server 不會維護資料表值參數資料行上的統計資料。
資料表值參數必須當做輸入 READONLY 參數傳遞給 Transact-SQL 常式。您不能在常式主體內針對資料表值參數執行 DML 作業，例如 UPDATE、DELETE 或 INSERT。
您無法使用資料表值參數做為 SELECT INTO 或 INSERT EXEC 語句的目標。數據表值參數可以位於 SELECT INTO 的 FROM 子句或 INSERT EXEC 字串或預存程式中。

Table-Valued 參數與 BULK INSERT 操作

使用資料表值參數可以和使用以集合為基礎之變數的其他方式相比較；但是，對於大型資料集而言，使用資料表值參數通常可以更快速。相較於具有比數據表值參數更大的啟動成本的大量作業，數據表值參數在插入不到 1000 個數據列時表現良好。

重複使用的資料表值參數會因為暫時資料表快取而獲益。此資料表快取相較於等效的 BULK INSERT 操作，能提供更佳的擴展性。藉由使用小規模列插入操作，您可以使用參數清單或批次語句來取得略微的效能優勢，而非 BULK INSERT 作業或數據表值參數。但是，這些方法在編寫程式時不太方便，並且當列增加時，效能會快速降低。

資料表值參數的執行效能等於或優於同等的參數陣列實作。

範例

下列範例會使用 Transact-SQL，並示範如何建立數據表值參數類型、宣告變數來參考它、填入參數清單，然後將值傳遞至預存程式。

USE AdventureWorks2012;  
GO  
  
/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  
  
/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE dbo. usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO AdventureWorks2012.Production.Location  
           (Name  
           ,CostRate  
           ,Availability  
           ,ModifiedDate)  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
        GO  
  
/* Declare a variable that references the type. */  
DECLARE @LocationTVP AS LocationTableType;  
  
/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT Name, 0.00  
    FROM AdventureWorks2012.Person.StateProvince;  
  
/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO

另請參閱

建立類型（Transact-SQL）
DECLARE @local_variable (Transact-SQL)
sys.types （Transact-SQL）
sys.parameters （Transact-SQL）
sys.parameter_type_usages （Transact-SQL）
CREATE PROCEDURE (Transact-SQL)
建立函數（Transact-SQL）

Last updated on 2017-06-13

共用方式為

使用 Table-Valued 參數（資料庫引擎）

優點

限制

Table-Valued 參數與 BULK INSERT 操作

範例

另請參閱

其他資源