SQL Server 2010 表变量参数(表值参数)用法

    表值参数是 SQL Server 二零零六中的新参数类型。表值参数是行使用户定义的表类型来声称的。使用表值参数,能够不必创建一时半刻表也许多参数,即可向
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. 动用 INSELX570T 语句填充表变量。有关怎样插入数据的详细音信,请参阅使用
    INSE猎豹CS六T 和 SELECT 添加行。
  5. 开创并填充表变量后,能够将该变量传递给例程。

    例程超出功效域后,表值参数将不再可用。类型定义则会直接保存,直到被删除。

若要在 SQL Server Native Client 中接纳表值参数,请参阅Table-Valued
Parameters (SQL Server Native Client)。

若要在 ADO.NET 中选择表值参数,请参阅 ADO.NET 文书档案。

优点

表值参数具有越来越高的八面玲珑,在少数境况下,可比最近表或任何传递参数列表的法门提供越来越好的质量。表值参数具有以下优势:

  • 第2遍从客户端填充数据时,不到手锁。
  • 提供简单的编制程序模型。
  • 允许在单个例程中回顾复杂的事务逻辑。
  • 减掉到服务器的过往。
  • 能够拥有不相同基数的表结构。
  • 是强类型。
  • 使客户端能够钦命排序依次和唯一键。

限制

表值参数有下边包车型大巴限定:

  • SQL Server 不维护表值参数列的总结音讯。
  • 表值参数必须作为输入 READONLY 参数字传送递到 Transact-SQL
    例程。不能够在例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSE中华VT
    那样的 DML 操作。
  • 无法将表值参数用作 SELECT INTO 或 INSE奇骏T EXEC
    语句的目的。表值参数能够在 SELECT INTO 的 FROM 子句中,也足以在
    INSE奥迪Q5T EXEC 字符串或存款和储蓄进度中。

作用域

SQL Server,就如任何参数①样,表值参数的成效域也是储存进程、函数或动态 Transact-SQL
文本。同样,表类型变量也与使用 DECLARE
语句制造的任何任何部分变量1样拥有效率域。能够在动态 Transact-SQL
语句内证明表值变量,并且能够将那个变量作为表值参数字传送递到存款和储蓄进度和函数。

安全性

表值参数的权能行使下列 Transact-SQL 关键字来遵从 SQL Server
的对象安全方式:CREATE、GRANT、DENY、ALTE途观、CONTROL、TAKE
OWNE福睿斯SHIP、REFERENCES、EXECUTE、VIEW DEFINITION 和 REVOKE。

目录视图

若要获取与表值参数关联的新闻,能够查询下列目录视图:sys.parameters
(Transact-SQL)、sys.types (Transact-SQL) 和 sys.table_types
(Transact-SQL)。

表值参数与 BULK INSE奥迪Q7T 操作

表值参数的使用格局与其余依据数据集的变量的应用方法一般;然而,频仍使用表值参数将比大型数据集要快。大容积操作的启航开发比表值参数大,与之比较,表值参数在插入数目少于
一千 的行时具有很好的推行品质。

引用的表值参数可从一时表缓存中收益。这一表缓存效能可比对等的 BULK INSERubiconT
操作提供更好的伸缩性。使用微型行插入操作时,能够因此使用参数列表或批量言语(而不是
BULK INSE本田UR-VT
操作或表值参数)来收获小的习性立异。可是,这几个办法在编制程序上不太有利,并且随着行的增多,质量会飞快回落。

表值参数在执行质量上与对等的参数阵列完结格外甚至更好。

下表表明依据插入操作的进程应运用哪一类技术。

数据源 服务器逻辑 行数 最佳技术

服务器上带格式的数据文件

直接插入

< 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

相关文章