一.摘要

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

二.簡介

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

三.描述

　　 計劃分三部分描述表值參數(shù)的應用。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

　　 (6) 一個用戶自定義表類型不允許作為表值型參數(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)點

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

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

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

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

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

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

(6)是強類型。

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

3. 限制

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

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

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

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

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

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

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

4. 作用域

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

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

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

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

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

例如：會計系統(tǒng)的選擇的多個部門多個科目或多個部所時，利用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對象，將其作為參數(shù)傳給存貯過程