SQL Server 2008 表變數參數(表值參數)用法

標籤：

    表值參數是 SQL Server 2008 中的新參數類型。表值參數是使用使用者定義的表類型來聲明的。使用表值參數，可以不必建立暫存資料表或許多參數，即可向 Transact-SQL 陳述式或常式（如預存程序或函數）發送多行資料。

    表值參數與 OLE DB 和 ODBC 中的參數數組類似，但具有更高的靈活性，且與 Transact-SQL 的整合更緊密。表值參數的另一個優勢是能夠參與基於資料集的操作。

    (注意：Transact-SQL 通過引用向常式傳遞表值參數，以避免建立輸入資料的副本。)

在 Transact-SQL 中建立和使用表值參數

表值參數具有兩個主要部分：SQL Server 類型以及引用該類型的參數。若要建立和使用表值參數，請執行以下步驟：

1. 建立表類型並定義表結構。
   有關如何建立 SQL Server 類型的資訊，請參閱使用者定義表類型。有關如何定義表結構的詳細資料，請參閱 CREATE TABLE (Transact-SQL)。
2. 聲明具有表型別參數的常式。有關 SQL Server 常式的詳細資料，請參閱 CREATE PROCEDURE (Transact-SQL) 和 CREATE FUNCTION (Transact-SQL)。
3. 聲明表類型變數，並引用該表類型。有關如何聲明變數的資訊，請參閱 DECLARE @local_variable (Transact-SQL)。
4. 使用 INSERT 語句填充表變數。有關如何插入資料的詳細資料，請參閱使用 INSERT 和 SELECT 添加行。
5. 建立並填充表變數後，可以將該變數傳遞給常式。
   
   常式超出範圍後，表值參數將不再可用。類型定義則會一直保留，直到被刪除。

若要在 SQL Server Native Client 中使用表值參數，請參閱Table-Valued Parameters (SQL Server Native Client)。

若要在 ADO.NET 中使用表值參數，請參閱 ADO.NET 文檔。

優點

表值參數具有更高的靈活性，在某些情況下，可比暫存資料表或其他傳遞參數列表的方法提供更好的效能。表值參數具有以下優勢：

• 首次從用戶端填充資料時，不擷取鎖。
• 提供簡單的編程模型。
• 允許在單個常式中包括複雜的商務邏輯。
• 減少到伺服器的往返。
• 可以具有不同基數的表結構。
• 是強型別。
• 使用戶端可以指定排序次序和唯一鍵。

限制

表值參數有下面的限制：

• SQL Server 不維護表值參數列的統計資訊。
• 表值參數必須作為輸入 READONLY 參數傳遞到 Transact-SQL 常式。不能在常式體中對錶值參數執行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。
• 不能將表值參數用作 SELECT INTO 或 INSERT EXEC 語句的目標。表值參數可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字串或預存程序中。

範圍

就像其他參數一樣，表值參數的範圍也是預存程序、函數或動態 Transact-SQL 文本。同樣，表類型變數也與使用 DECLARE 語句建立的其他任何局部變數一樣具有範圍。可以在動態 Transact-SQL 陳述式內聲明表值變數，並且可以將這些變數作為表值參數傳遞到預存程序和函數。

安全性

表值參數的許可權使用下列 Transact-SQL 關鍵字來遵循 SQL Server 的對象安全模式：CREATE、GRANT、DENY、ALTER、CONTROL、TAKE OWNERSHIP、REFERENCES、EXECUTE、VIEW DEFINITION 和 REVOKE。

目錄檢視

若要擷取與表值參數關聯的資訊，可以查詢下列目錄檢視：sys.parameters (Transact-SQL)、sys.types (Transact-SQL) 和 sys.table_types (Transact-SQL)。

表值參數與 BULK INSERT 操作

表值參數的使用方法與其他基於資料集的變數的使用方法相似；但是，頻繁使用表值參數將比大型資料集要快。大容量操作的啟動開銷比表值參數大，與之相比，表值參數在插入數目少於 1000 的行時具有很好的執行效能。

重用的表值參數可從暫存資料表緩衝中受益。這一表緩衝功能可比對等的 BULK INSERT 操作提供更好的伸縮性。使用小型行插入操作時，可以通過使用參數列表或批量語句（而不是 BULK INSERT 操作或表值參數）來獲得小的效能改進。但是，這些方法在編程上不太方便，並且隨著行的增加，效能會迅速下降。

表值參數在執行效能上與對等的參數陣列實現相當甚至更好。

下表說明根據插入操作的速度應使用哪種技術。

資料來源	伺服器邏輯	行數	最佳技術
伺服器上帶格式的資料檔案	直接插入	< 1000	BULK INSERT
伺服器上帶格式的資料檔案	直接插入	> 1000	BULK INSERT
伺服器上帶格式的資料檔案	複雜	< 1000	表值參數
伺服器上帶格式的資料檔案	複雜	> 1000	BULK INSERT
遠程用戶端進程	直接插入	< 1000	表值參數
遠程用戶端進程	直接插入	> 1000	BULK INSERT
遠程用戶端進程	複雜	< 1000	表值參數
遠程用戶端進程	複雜	> 1000	表值參數

 

 

樣本

下面的樣本使用 Transact-SQL 並示範如何執行以下操作：建立表值參數類型，聲明變數來引用它，填充參數列表，然後將值傳遞到預存程序。

USE AdventureWorks;GO/*建立表值參數類型 */CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50), CostRate INT );GO/* 建立一個過程來獲得該表值參數資料 */CREATE PROCEDURE usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO [AdventureWorks].[Production].[Location] ([Name] ,[CostRate] ,[Availability] ,[ModifiedDate]) SELECT *, 0, GETDATE() FROM @TVP; GO/*聲明一個變數來引用該類型*/DECLARE @LocationTVP AS LocationTableType;/* Add data to the table variable. */INSERT INTO @LocationTVP (LocationName, CostRate) SELECT [Name], 0.00 FROM [AdventureWorks].[Person].[StateProvince];/* 把表變數的資料傳遞給預存程序 */EXEC usp_InsertProductionLocation @LocationTVP;GO

SQL Server 2008 表變數參數(表值參數)用法