一.摘要

　　表值參數(shù)（Table-valued parameters）簡(jiǎn)稱TVP，是SQL Server 2008中引入的一種新特性，它提供了一種內(nèi)置的方式，讓客戶端應(yīng)用可以只通過單獨(dú)的一條參化數(shù)SQL語句，就可以向SQL Server發(fā)送多行數(shù)據(jù)。

二.簡(jiǎn)介

　　 在表值參數(shù)出現(xiàn)以前，當(dāng)需要發(fā)送多行數(shù)據(jù)到SQL Server，我們只能使用一些替代方案來實(shí)現(xiàn)：

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

　　 使用這一方法，可以被傳遞的數(shù)據(jù)總量受限于可用參數(shù)的個(gè)數(shù)。SQL Server的存儲(chǔ)過程最多可以使用2100個(gè)參數(shù)。

　　 在這種方法中，服務(wù)端邏輯必須將這些獨(dú)立的值組合到表變量中，或是臨時(shí)表中進(jìn)行處理。

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

　　 這種方式要求存儲(chǔ)過程或語句中要有必要的數(shù)據(jù)結(jié)構(gòu)驗(yàn)證和數(shù)據(jù)松綁的邏輯。

　　 (3) 為多行數(shù)據(jù)的修改創(chuàng)建一系列獨(dú)立的SQL語句。

　　 就像在一個(gè)SqlDataAdapter中調(diào)用Update方法時(shí)產(chǎn)生的那些一樣，這些更新可以被獨(dú)立地或是分組成批地提交到服務(wù)器。

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

　　 (4) 使用bcp實(shí)用程序或是使用SqlBulkCopy對(duì)象將多行數(shù)據(jù)載入一個(gè)表中。

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

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

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

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

　　 (3) 表值參數(shù)的優(yōu)點(diǎn)在于你可以向存儲(chǔ)過程或函數(shù)發(fā)送多行數(shù)據(jù)，而無需向以前那樣必須聲明多個(gè)參數(shù)或者使用XML參數(shù)類型來處理多行數(shù)據(jù)。

三.描述

　　 計(jì)劃分三部分描述表值參數(shù)的應(yīng)用。

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

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

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

四.第一部分：在T-SQL中創(chuàng)建和使用TVP

　　 1. 表值參數(shù)具有兩個(gè)主要部分：SQL Server 類型以及引用該類型的參數(shù)，若要?jiǎng)?chuàng)建和使用表值參數(shù)，請(qǐng)執(zhí)行以下步驟：

　　 (1) 創(chuàng)建表類型并定義表結(jié)構(gòu)。

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

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

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

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

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

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

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

　　 (5) 用戶自定義函數(shù)不能以用戶定義表類型中的計(jì)算列定義為參數(shù)來調(diào)用。

　　 (6) 一個(gè)用戶自定義表類型不允許作為表值型參數(shù)來調(diào)用用戶自定義函數(shù)。

　　例如：

/* Create a user-defined table type */

CREATE TYPE OrderItem$Udt AS TABLE(

OrderId int primary key,

CustomerId int,

　　 OrderedAt datetime

　　 )

　　 GO

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

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) 創(chuàng)建并填充表變量后，可以將該變量傳遞給例程。

　　 EXEC dbo.OrderItem$Insert @OrderItemUdt,@OrderDetailUdt

SELECT * FROM dbo.OrderItem

2. 優(yōu)點(diǎn)

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

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

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

(3)允許在單個(gè)例程中包括復(fù)雜的業(yè)務(wù)邏輯。

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

(5)可以具有不同基數(shù)的表結(jié)構(gòu)。

(6)是強(qiáng)類型。

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

3. 限制

表值參數(shù)有下面的限制：

(1) SQL Server 不維護(hù)表值參數(shù)列的統(tǒng)計(jì)信息。

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

　 不能在例程體中對(duì)表值參數(shù)執(zhí)行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。

***如果想要修改那些已經(jīng)傳入到存儲(chǔ)過程或參數(shù)化語句中的表值型參數(shù)中的數(shù)據(jù)，只能通過向臨時(shí)表或表變量中插入數(shù)據(jù)來實(shí)現(xiàn)。

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

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

4. 作用域

(1) 就像其他參數(shù)一樣，表值參數(shù)的作用域也是存儲(chǔ)過程、函數(shù)或動(dòng)態(tài) Transact-SQL 文本。

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

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

應(yīng)用比較廣泛的是在Browse Master的多行數(shù)據(jù)作為過濾條件時(shí)使用。

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

例如：會(huì)計(jì)系統(tǒng)的選擇的多個(gè)部門多個(gè)科目或多個(gè)部所時(shí)，利用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

六.其他

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