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

    表值参数是 SQL Server 2008
中的新参数类型。表值参数是采取用户定义的表类型来声称的。使用表值参数,可以无需创建临时表或多参数,即可为
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. 动用 INSERT 语句填入充表变量。有关如何插入数据的详细信息,请参见使用
    INSERT 和 SELECT 添加行。
  5. 创立并填写充表变量后,可以用欠变量传递让例程。

    例程超出作用域后,表值参数将不再可用。类型定义则会一直保留,直到于剔除。

一经要以 SQL Server Native Client 中以表值参数,请参阅Table-Valued
Parameters (SQL Server Native Client)。

只要一旦于 ADO.NET 中使用表值参数,请参见 ADO.NET 文档。

优点

表值参数有双重胜似之油滑,在好几情况下,可于临时表或其他传递参数列表的章程提供再好的习性。表值参数有以下优势:

  • 首涂鸦从客户端填充数据经常,不获锁。
  • 提供简的编程模型。
  • 容在么例程中概括复杂的事体逻辑。
  • 调减及服务器的过往。
  • 可以有不同基数的阐发结构。
  • 是强类型。
  • 设客户端好指定排序依次和唯一键。

限制

表值参数有下面的克:

  • SQL Server 不保护表值参数列的统计信息。
  • 表值参数必须作为输入 READONLY 参数传递到 Transact-SQL
    例程。不克以例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSERT
    这样的 DML 操作。
  • 勿可知将表值参数用作 SELECT INTO 或 INSERT EXEC
    语句的对象。表值参数可以于 SELECT INTO 的 FROM 子句被,也足以当
    INSERT EXEC 字符串或存储过程中。

作用域

就是如另参数一样,表值参数的作用域也是储存过程、函数或动态 Transact-SQL
文本。同样,表类型变量也和用 DECLARE
语句创建的另任何部分变量一样享有作用域。可以在动态 Transact-SQL
语句内声明表值变量,并且可将这些变量作为表值参数传递至囤过程及函数。

安全性

表值参数的权能行使下列 Transact-SQL 关键字来遵循 SQL Server
的靶子安全模式:CREATE、GRANT、DENY、ALTER、CONTROL、TAKE
OWNERSHIP、REFERENCES、EXECUTE、VIEW DEFINITION 和 REVOKE。

目录视图

要一旦博得和表值参数关联的信息,可以查询下列目录视图:sys.parameters
(Transact-SQL)、sys.types (Transact-SQL) 和 sys.table_types
(Transact-SQL)。

表值参数与 BULK INSERT 操作

表值参数的动方法与外因数据集的变量的下办法一般;但是,频繁利用表值参数将于大型数据集要快。大容量操作的起步开发比较表值参数大,与的相比,表值参数在插入数目少
1000 的流行具有很好的施行性。

用的表值参数可从临时表缓存中受益。这无异申明缓存功能而正如对顶的 BULK INSERT
操作提供再好之伸缩性。使用微型行插入操作时,可以透过采用参数列表或批量口舌(而休是
BULK INSERT
操作还是表值参数)来取得多少之性改进。但是,这些主意在编程上不顶方便,并且随着实践之加码,性能会飞速下滑。

表值参数在实施性上及针对顶之参数阵列实现相当甚至又好。

下表说明根据插入操作的速度应下啊种技术。

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

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

直接插入

< 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

相关文章