使用資料表值參數 (Database Engine)

  • 發行項

適用於： SQL Server Azure SQL Database Not supported. Azure Synapse Analytics Not supported. Analytics Platform System (PDW)

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

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

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

本主題內容：

優點

限制

資料表值參數與BULK INSERT 作業的比較

範例

優點

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

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

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

權限

若要建立使用者定義資料表類型的執行個體，或使用資料表值參數呼叫預存程序，使用者必須對該類型具有 EXECUTE 和 REFERENCES 權限，或對包含該類型的結構描述或資料庫具有這些權限。

限制

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

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

資料表值參數與BULK INSERT 作業的比較

使用資料表值參數可以和使用以集合為基礎之變數的其他方式相比較；但是，對於大型資料集而言，使用資料表值參數通常可以更快速。 與大量作業 (其啟動成本高於資料表值參數) 相較之下，當插入 1000 個以下的資料列時，資料表值參數會有很不錯的執行效能。

重複使用的資料表值參數會因為暫存資料表快取而獲益。 這種資料表快取提供了比同等的 BULK INSERT 作業更好的延展性。 藉由使用小型資料列插入作業，可能會因為使用參數清單或批次處理的陳述式 (而非 BULK INSERT 作業或資料表值參數) 而有效能上的小獲益。 但是，這些方法的便利性不如程式，而且當資料列增加時，效能會快速降低。

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

範例

下列範例會使用 Transact-SQL 並示範如何建立資料表值參數類型、宣告變數來參考該類型、填入參數清單，然後將值傳遞給 AdventureWorks 資料庫中的預存程序。

/* 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 AdventureWorks2022.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 AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

另請參閱