一.摘要

　　表值參數（Table-valued parameters）簡稱TVP，是SQL Server 2008中引入的一種新特性，它提供了一種內置的方式，讓客戶端應用可以只通過單獨的一條參化數SQL語句，就可以向SQL Server發送多行數據。

二.簡介

　　 在表值參數出現以前，當需要發送多行數據到SQL Server，我們只能使用一些替代方案來實現：

　　 (1) 使用一連串的獨立參數來表示多列和多行數據的值。

　　 使用這一方法，可以被傳遞的數據總量受限于可用參數的個數。SQL Server的存儲過程最多可以使用2100個參數。

　　 在這種方法中，服務端邏輯必須將這些獨立的值組合到表變量中，或是臨時表中進行處理。

　　 (2) 將多個數據值捆綁到帶限定符的字符串或是XML文檔中，然后再將文本值傳遞到一個存儲過程或語句中。

　　 這種方式要求存儲過程或語句中要有必要的數據結構驗證和數據松綁的邏輯。

　　 (3) 為多行數據的修改創建一系列獨立的SQL語句。

　　 就像在一個SqlDataAdapter中調用Update方法時產生的那些一樣，這些更新可以被獨立地或是分組成批地提交到服務器。

　　 不過，盡管成批提交中含有多重語句，但這些語句在服務端都是被分開獨立執行的。

　　 (4) 使用bcp實用程序或是使用SqlBulkCopy對象將多行數據載入一個表中。

　　 盡管這一技術效率很高，但它并不支持在服務端執行（注：多行數據仍然無法一次性傳給存儲過程），除非數據是被載入到臨時表或是表變量中。

　　 SQL Server 2008中的T-SQL功能新增了表值參數。利用這個新增特性，我們可以很方便地通過T-SQL語句，或者通過一個應用程序，將一個表作為參數傳給函數或存儲過程。

　　 (1) 表值參數表示你可以把一個表類型作為參數傳遞到函數或存儲過程里。

　　 (2) 表值參數的功能可以允許你向被聲明為T-SQL變量的表中導入數據，然后把該表作為一個參數傳遞到存儲過程或函數中去。

　　 (3) 表值參數的優點在于你可以向存儲過程或函數發送多行數據，而無需向以前那樣必須聲明多個參數或者使用XML參數類型來處理多行數據。

三.描述

　　 計劃分三部分描述表值參數的應用。

　　 (1) 在T-SQL中創建和使用TVP

　　 (2) 在ADO.NET中利用DataTable對象，將其作為參數傳給存貯過程

　　 (3) 在ADO.NET中利用Collection對象，將其作為參數傳給存貯過程

四.第一部分：在T-SQL中創建和使用TVP

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

　　 (1) 創建表類型并定義表結構。

　　 TVP功能的基礎是SQL2008中最新的用戶自定義表類型（User-Defined Table Types）,簡稱UDTT，它允許用戶將表的定義注冊為全局周知類型。

　　 注冊之后，這些表類型可以像本地變量一樣用于批處理中、以及存儲過程的函數體中，也就是UDTT的變量可以作為參數在存儲過程和參數化TSQL中使用。

用戶自定義表類型的使用有許多限制：

　　 (1) 一個用戶自定義表類型不允許用來定義表的列類型，也不能用來定義一個用戶自定義結構類型的字段。

　　 (2) 不允許在一個用戶自定義表類型上創建一個非聚合索引，除非這個索引是基于此用戶自定義表類型創建的主鍵或唯一約束。

　　 (3) 在用戶自定義表類型的定義中，不能指定缺省值。

　　 (4) 一旦創建后，就不允許再對用戶自定義表類型的定義進行修改。

　　 (5) 用戶自定義函數不能以用戶定義表類型中的計算列定義為參數來調用。

　　 (6) 一個用戶自定義表類型不允許作為表值型參數來調用用戶自定義函數。

　　例如：

/* Create a user-defined table type */

CREATE TYPE OrderItem$Udt AS TABLE(

OrderId int primary key,

CustomerId int,

　　 OrderedAt datetime

　　 )

　　 GO

(2) 聲明具有表類型參數的例程。

CREATE PROCEDURE OrderItem$Insert(

　　 @OrderHeaders AS OrderItem$Udt READONLY,

@OrderDetails AS OrderDetail$Udt READONLY)

AS

BEGIN

-- Bulk insert order header rows from TVP

INSERT INTO [OrderItem]

　　 SELECT *, SYSDATETIME() FROM @OrderHeaders

　　 -- Bulk insert order detail rows from TVP

INSERT INTO [OrderDetail]

　　 SELECT *, SYSDATETIME() FROM @OrderDetails

　　 END

　　 GO

(3) 聲明表類型變量，并引用該表類型。

　　 IF OBJECT_ID (N'OrderItem', N'U') IS NOT NULL

DROP TABLE [OrderItem]

GO

CREATE TABLE [OrderItem](

OrderId int NOT NULL primary key,

CustomerId int NOT NULL,

　　 OrderedAt datetime NOT NULL,

　　 CreatedAt datetime2(0) NOT NULL DEFAULT sysdatetime()

)

GO

(4) 使用 INSERT 語句填充表變量。

DECLARE @OrderItemUdt dbo.OrderItem$Udt

INSERT INTO @OrderItemUdt

VALUES (1,20,GETDATE()),(2,31,GETDATE()),(100,4,GETDATE()),(201,51,GETDATE())

SELECT * FROM @OrderItemUdt　

(5) 創建并填充表變量后，可以將該變量傳遞給例程。

　　 EXEC dbo.OrderItem$Insert @OrderItemUdt,@OrderDetailUdt

SELECT * FROM dbo.OrderItem

2. 優點

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

(1)首次從客戶端填充數據時，不獲取鎖。

(2)提供簡單的編程模型。

(3)允許在單個例程中包括復雜的業務邏輯。

(4)減少到服務器的往返。

(5)可以具有不同基數的表結構。

(6)是強類型。

(7)使客戶端可以指定排序順序和唯一鍵。

3. 限制

表值參數有下面的限制：

(1) SQL Server 不維護表值參數列的統計信息。

(2) 表值參數必須作為輸入 READONLY 參數傳遞到 Transact-SQL 例程。

　 不能在例程體中對表值參數執行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。

***如果想要修改那些已經傳入到存儲過程或參數化語句中的表值型參數中的數據，只能通過向臨時表或表變量中插入數據來實現。

  (3) 不能將表值參數用作 SELECT INTO 或 INSERT EXEC 語句的目標。

  表值參數可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存儲過程中。

4. 作用域

(1) 就像其他參數一樣，表值參數的作用域也是存儲過程、函數或動態 Transact-SQL 文本。

(2) 表類型變量也與使用 DECLARE 語句創建的其他任何局部變量一樣具有作用域。可以在動態 Transact-SQL 語句內聲明表值變量，并且可以將這些變量作為表值參數傳遞到存儲過程和函數。

(3) 一般多用于行數小于1000行的數據。

應用比較廣泛的是在Browse Master的多行數據作為過濾條件時使用。

利用TVP使得一次插入多項或Select多行變得大為簡單。過去，我們使用笨拙的逗號分隔列表或XML，雖其能夠勝任，但不是以習慣的對象方式存在，而且存取速度也很慢。

例如：會計系統的選擇的多個部門多個科目或多個部所時，利用TVP的方式可以大大提高存取的速度也可提高編程的可讀性。

五. 例子

USE AdventureWorks

GO

------------------------

IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id

WHERE st.name = N'OrderItem$Udt' AND ss.name = N'dbo')

DROP TYPE [dbo].[OrderItem$Udt]

GO

CREATE TYPE OrderItem$Udt AS TABLE(

OrderId int primary key,

CustomerId int,

OrderedAt datetime)

GO

------------------------

IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id

WHERE st.name = N'OrderDetail$Udt' AND ss.name = N'dbo')

DROP TYPE [dbo].[OrderDetail$Udt]

GO

CREATE TYPE OrderDetail$Udt AS TABLE(

OrderId int,

LineNumber int primary key(OrderId,LineNumber),

ProductId int,

Quantity int,

Price money)

GO

---------------------------

IF OBJECT_ID (N'OrderItem', N'U') IS NOT NULL

DROP TABLE [OrderItem]

GO

CREATE TABLE [OrderItem](

OrderId int NOT NULL primary key,

CustomerId int NOT NULL,

OrderedAt datetime NOT NULL,

CreatedAt datetime2(0) NOT NULL DEFAULT sysdatetime()

)

GO

--------------------------

IF OBJECT_ID (N'OrderDetail', N'U') IS NOT NULL

DROP TABLE [OrderDetail]

GO

CREATE TABLE [OrderDetail](

OrderId int NOT NULL,

LineNumber int NOT NULL primary key(OrderId,LineNumber),

ProductId int NOT NULL,

Quantity int NOT NULL,

Price money NOT NULL,

CreatedAt datetime2(0) NOT NULL DEFAULT sysdatetime())

GO

------------------------

IF OBJECT_ID(N'OrderItem$Insert',N'P') IS NOT NULL

DROP PROC OrderItem$Insert

GO

CREATE PROCEDURE OrderItem$Insert(

@OrderHeaders AS OrderItem$Udt READONLY,

@OrderDetails AS OrderDetail$Udt READONLY)

AS

BEGIN

-- Bulk insert order header rows from TVP

INSERT INTO [OrderItem]

SELECT *, SYSDATETIME() FROM @OrderHeaders

-- Bulk insert order detail rows from TVP

INSERT INTO [OrderDetail]

SELECT *, SYSDATETIME() FROM @OrderDetails

END

GO

------------------------

DECLARE @OrderItemUdt dbo.OrderItem$Udt

INSERT INTO @OrderItemUdt

VALUES (1,20,GETDATE()),(2,31,GETDATE()),(100,4,GETDATE()),(201,51,GETDATE())

SELECT * FROM @OrderItemUdt

------------------------

DECLARE @OrderDetailUdt dbo.OrderDetail$Udt

INSERT INTO @OrderDetailUdt

VALUES (1,1,11,111,1111),(1,2,12,121,1212.12),(1,3,13,131,1313.13),

(2,1,21,211,2121),(2,2,22,222,2222.22),(2,3,23,231,2323.23),

(100,1,101,1001,1001.1001),(100,2,102,1002,1002.1002),(100,3,103,1003,1003.1003),

(201,1,2011,2011,201.201),(201,2,2012,2012,2012.2012)

SELECT * FROM @OrderDetailUdt

------------------------

EXEC dbo.OrderItem$Insert @OrderItemUdt,@OrderDetailUdt

SELECT * FROM dbo.OrderItem

SELECT * FROM dbo.OrderDetail

GO

六.其他

　　下一部分的內容為：Table-values parameter(TVP)系列之二: 在ADO.NET中利用DataTable對象，將其作為參數傳給存貯過程