表值参数

表值参数是 SQL Server 2008 中的新参数类型。表值参数是使用用户定义的表类型来声明的。使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据。
  表值参数与 OLE DB 和 ODBC 中的参数数组类似，但具有更高的灵活性，且与 Transact-SQL 的集成更紧密。表值参数的另一个优势是能够参与基于数据集的操作。

在 Transact-SQL 中创建和使用表值参数

表值参数具有两个主要部分：SQL Server 类型以及引用该类型的参数。若要创建和使用表值参数，请执行以下步骤：
  1.创建表类型并定义表结构。
  2.声明具有表类型参数的例程(CREATE PROCEDURE/FUNCTION)。
  3.声明表类型变量，并引用该表类型。
  4.使用INSERT语句填充表变量。
  5.创建并填充表变量后，可以将该变量传递给例程。

优点
  表值参数具有更高的灵活性，在某些情况下，可比临时表或其他传递参数列表的方法提供更好的性能。表值参数具有以下优势：

• 首次从客户端填充数据时，不获取锁。
• 提供简单的编程模型。
• 允许在单个例程中包括复杂的业务逻辑。
• 减少到服务器的往返。
• 可以具有不同基数的表结构。
• 是强类型。
• 使客户端可以指定排序顺序和唯一键。

限制
  表值参数有下面的限制：

• SQL Server 不维护表值参数列的统计信息。
• 表值参数必须作为输入 READONLY 参数传递到 Transact-SQL 例程。不能在例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。
• 不能将表值参数用作 SELECT INTO 或 INSERT EXEC 语句的目标。表值参数可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存储过程中。
• 不能将表值参数指定为 INSERT EXEC 语句的目标；但是，可以将它指定为 INSERT EXEC 字符串或存储过程中的源。

作用域
  就像其他参数一样，表值参数的作用域也是存储过程、函数或动态 Transact-SQL 文本。同样，表类型变量也与使用 DECLARE 语句创建的其他任何局部变量一样具有作用域。可以在动态 Transact-SQL 语句内声明表值变量，并且可以将这些变量作为表值参数传递到存储过程和函数。

用户定义表类型

在 SQL Server 2008 中，用户定义表类型是指用户所定义的表示表结构定义的类型。您可以使用用户定义表类型为存储过程或函数声明表值参数，或者声明您要在批处理中或在存储过程或函数的主体中使用的表变量。
  若要创建用户定义表类型，请使用CREATE TYPE语句。为了确保用户定义表类型的数据满足特定要求，您可以对用户定义表类型创建唯一约束和主键。
  用户定义表类型具有下列限制：

• 用户定义表类型不能用作表中的列或结构化用户定义类型中的字段。
• 基于用户定义表类型的别名类型(详见CREATE TYPE语句)
• [NOT FOR REPLICATION] 选项是不允许的。
• CHECK 约束要求保留计算列。
• 计算列的主键必须是 PERSISTED 和 NOT NULL。
• 无法对用户定义表类型创建非聚集索引，除非该索引是对用户定义表类型创建 PRIMARY KEY 或 UNIQUE 约束的结果。（SQL Server 使用索引强制实施任何 UNIQUE 或 PRIMARY KEY 约束。）
• 在创建用户定义表类型定义后不能对其进行修改。
• 不能在用户定义表类型的计算列的定义中调用用户定义函数。

【示例】
(1)定义用户表值参数

USE AdventureWorks2008R2;
GO

/* Create a user-defined table type */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

(2)下面的示例使用 Transact-SQL 并演示如何执行以下操作：创建表值参数类型，声明变量来引用它，填充参数列表，然后将值传递到存储过程。

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50),
  CostRate INT )
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO AdventureWorks2008R2.Production.Location
           (Name,
            CostRate,
            Availability,
            ModifiedDate)
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT Name, 0.00
    FROM AdventureWorks2008R2.Person.StateProvince;

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

(3)

CREATE TYPE SalesByStore_tbl AS TABLE 
     (titleid varchar(80) NOT NULL PRIMARY KEY,
      qty     smallint    NOT NULL)
go
CREATE PROCEDURE SalesByStore @storeid varchar(30) AS
   DECLARE @ret SalesByStore_tbl
   INSERT @ret (titleid, qty)
      SELECT t.titleid, s.store_id 
      FROM   sales s
      JOIN   titles t ON t.titleid = s.titleid
      WHERE  s.store_id = @storeid
   SELECT * FROM @ret
go
CREATE PROCEDURE BigSalesByStore @storeid varchar(30),
                                 @qty     smallint AS
   DECLARE @data SalesByStore_tbl
   INSERT @data
      EXEC SalesByStore @storeid
   SELECT titleid, qty FROM @data WHERE qty>= @qty
go
EXEC SalesByStore '7131'
EXEC BigSalesByStore '7131', 25
go
DROP PROCEDURE SalesByStore, BigSalesByStore
DROP TYPE SalesByStore_tbl

表值参数与 BULK INSERT 操作

表值参数的使用方法与其他基于数据集的变量的使用方法相似，但是，频繁使用表值参数将比大型数据集要快。大容量操作的启动开销比表值参数大，与之相比，表值参数在插入数目少于 1000 的行时具有很好的执行性能。
  重用的表值参数可从临时表缓存中受益。这一表缓存功能可比对等的 BULK INSERT 操作提供更好的伸缩性。使用小型行插入操作时，可以通过使用参数列表或批量语句（而不是 BULK INSERT 操作或表值参数）来获得小的性能改进。但是，这些方法在编程上不太方便，并且随着行的增加，性能会迅速下降。
  表值参数在执行性能上与对等的参数阵列实现相当甚至更好。下表说明根据插入操作的速度应使用哪种技术。

作者：zoyoto
链接：https://www.jianshu.com/p/59d3b069e5e4
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。