Table-values parameter(TVP)系列之一:在T-SQL中创造和动TVP

一.摘要
  表值参数(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
  参看URL:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/5e95a382-1e01-4c74-81f5-055612c2ad99.htm
  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对象,将那看成参数传于存贮过程

相关文章