SQL Server中的表值参数

在SQL Server的最新更新中, 表值参数用于将来自多行的数据传输到存储在表单、过程或函数中的T-SQL语句.

通过在一条语句或例程中using多条记录,可以减少与服务器的往返. 您可以using用户定义的表来声明它. In 2008, 用户定义表类型(udtt)和TVPs的初始化,表值参数(TVP)在SQL server中using. 在此之前,通过表传递变量是很有挑战性的. 因此,它们可以通过存储过程轻松地将变量作为参数. 您可以很容易地纠正这一点,并添加功能, 此功能在SQL Server 2008及以上版本中得到了增强. 通过using这个, 现在可以发送多行数据,并using恢复技术(如创建临时表)将其存储在过程或函数中, 或者通过一个参数传递多个值,作为一个表变量,可以作为一个参数传递. 在本博客中,九五至尊vi老品牌值得信赖将借助九五至尊vi老品牌值得信赖的 .网络开发团队. 看一看!

1. 在SQL (UDTT)中创建用户定义的表类型

可以usingusing模式创建的函数预定义表的用户定义. 九五至尊vi老品牌值得信赖也称其为模式定义,可以using临时数据(也称为udtt(用户定义的表类型))创建。.

这些用户定义的表类型几乎支持与普通数据表相同的特性,比如主键, 默认值, 独特的约束, 等. 它是从一个可以通过存储过程和函数等参数传递的表中引用的.

对于表值参数,九五至尊vi老品牌值得信赖需要一个用户定义的表类型(UDTT).  可以using下面的T-SQL语句创建UDTT.

创建类型UDTT_国家作为表(
 
    国家的名字 nvarchar(100),
 
    Currency的名字 nvarchar(50)
 
)
GO

注意: 创建用户定义的表类型的语法与执行正常创建的表类似. 如九五至尊vi老品牌值得信赖所知,没有可用的用户界面 SQL server 管理工作室. 主键, 索引, 约束, 计算列, 可以在UDDT定义中声明标识列. 但是,外键不可用.

用户定义的表类型(UDTT)没有可用的ALTER语句. 对于修改,九五至尊vi老品牌值得信赖需要usingDROP和CREATE.

2. 存储过程中的表值参数

表值参数的using几乎等同于其他参数. 对于其他数据类型,九五至尊vi老品牌值得信赖必须给UDTT一个名称.  表值参数允许九五至尊vi老品牌值得信赖将多个列和行作为输入传递给存储的方法.

表值参数必须通过READONLY参数传递. 所有DML操作,如INSERT、DELETE和UPDATE,都不能存储在任何过程中. 您只能using像SELECT语句这样的函数.

下面给出了存储过程中表值参数的示例. 表值参数不能用作存储过程中的OUTPUT参数.

创建过程USP_添加国家
 
    @国家UDTT_国家只读的
 
AS
 
开始
 
    插入国家Columns表 (国家的名字, Currency的名字)
 
    选择国家的名字,Currency的名字从 @国家
 
结束
 
GO

用户定义函数中表值形参的using与此类似.

3. 从T-SQL语句执行

执行一个存储过程, 哪个有表值参数, 九五至尊vi老品牌值得信赖需要创建一个表变量, 这引用通用.   下面是一个执行的例子.

从T-SQL语句执行

4. 从c#代码执行

执行存储过程 .net代码中,九五至尊vi老品牌值得信赖必须将参数定义为结构化参数.

Structure data type accepts 数据表, DbDataReader or IEnumarable. 在下面的例子中, 第一种方法是using数据表, while the second is using IEnumarable for Columns表 Records. 第三个例子展示了如何usingdapper来using表值参数.

using数据表

状态 无效 TableParameterUsing数据表()
{  数据表 dtCurrency =  数据表();
    dtCurrency..添加(“国家”, typeof(字符串));
    dtCurrency..添加(“Currencyname”, typeof(字符串));
    dtCurrency..添加(“印度”, “印度卢比”);
    dtCurrency..添加(“美国”, “美元”);
 
SqlConnection连接 = SQlConnection(连接String);
连接.开放();
SqlCommand cmd =  SqlCommand(“USP_添加国家”, 连接);
cmd.CommandType = CommandType.StoredProcedure;
 
//传递表值参数到存储过程
SqlParameter sqlparam = cmd.参数.添加WithValue(“@国家”, dtCurrency);
SqlParam.SqlDbtype = SqlDbType.结构化;
cmd.ExecuteNon查询();
连接.关闭();

usingColumns表

静态 无效 TableParameterUsingColumns表()
{
    / /本地函数
    IEnumerable<SqlDataRecord> 创建SQLDataRecords(IEnumerable<国家>) 国家)
   {
        SqlMetaData[] 元数据 =  Sql元数据[2];
        元数据[0] =  SqlMetaData(“国家name”, SqlDbType.NVarChar, 100);
       元数据[1] =  SqlMetaData(“Currencyname”, SqlDbtype.NVarchar,50);
       SqlDataRecord记录 =  SqlDataRecord(元数据);
       foreach (var c in 国家)
       {
           记录.SetSqlString(0, c.国家name); 
           记录.SetSqlString(1, c.Currencyname);
       };
  }
  Columns表<国家> currecnyColumns表 =  Columns表<国家>
  {
           国家(“印度”, “印度卢比”),
           国家(“美国”, “美元”),
  };
IEnumerable<SqlDataRecord> sqlDataRecords = 创建SqlDataRecords(currencyColumns表);
 
SqlConnection连接 =  SqlConnection(连接String);
连接.开放();
SqlCommand cmd =  SqlCommand(“USP_添加国家”, 连接);
cmd.CommandType = CommandType.StoredProcedure;
 
//传递表值参数到存储过程
Sqlparameter sqlParam = cmd.参数.添加Withvalue(“@国家”, sqlDataRecords);
sqlParam.SqlDbtype = SqlDbtype.结构化;
cmd.ExecuteNon查询();
连接.关闭();
}

using短小精悍的

静态 无效 TableparameterUsingDapper()
{ 
     Columns表<国家> currencyColumns表 =  Columns表<国家>
       {
             国家(“印度”, “印度卢比”),
             国家(“美国”, “美元”)
};
数据表 dtCurrency =  数据表();
using (var 读者 = ObjectReader.创建(currencyColumns表))
{
      dtCurrecny.负载(读者);
}
//对于数据表,从这里开始
SqlConnection连接 =   SqlConnection(连接String);
动态参数的参数 =  动态参数();
参数.添加(“@国家”, dtCurrency.AsTableValuedParameter(“UDTT_国家”));
连接.查询(“USP_添加国家”, 参数, commandType: CommandType.StoredProcedure);
}

5. using表值参数修改数据(Transact-SQL)

执行基于集合的数据修改,通过执行单个语句查询影响行的不同方面. 您可以看到对表值参数的良好影响. 例如,您拥有选择相关行并将其添加到数据库表的所有权限. 您还可以执行DML操作,例如创建, 删除和更新表值参数,将其与需要升级的表连接.

下面描述的UPDATE语句解释了如何usingTransact-SQL通过执行与国家表的连接来using表值参数.

在using表值参数时,可以using从子句中的JOIN函数. 九五至尊vi老品牌值得信赖也可以将这个表值参数称为“Edited 国家”,如下图所示.

更新dbo.国家  
 
    集国家.国家的名字 = edited国家.国家的名字  
 
    从dbo.国家 内连接 @tvpEdited国家作为edited国家  
 
    在dbo.国家.国家ID = edited国家.国家ID;

下面提到的Transact-SQL语句紧密地定义了如何从表值参数中选择特定的行集.

插入dbo.国家 (国家ID, 国家的名字)  
 
选择新.国家yID, 新国家.国家的名字@tvpNew国家作为新;

在上面的查询中,INSERT选项紧密地定义了一个基于集合的操作. 

6. using内存优化表值参数

这个内存优化的表值参数是using相同的内存优化算法管理数据结构和内存优化的表的有效方法. 这将通过从已编译的本地模块访问表变量来最大限度地提高进程的效率. 

using相同的概念, 启动内存优化的表值参数是很有可能的,这些表值参数的主要关注点是减少临时活动,并using内存优化的tvp.

下面的示例清楚地演示了内存优化的表值参数.

创建一个表
 
(国家Id INT主键非聚集哈希 (BUCKET_COUNT = 1000), 国家的名字 VARCHAR(100))( MEMORY_OPTIMIZED = ON )

对于任何语法, 如果看到MEMORY_OPTIMIZED=ON子句,则意味着这种类型的表类型是内存优化的. 另外, 您还可以创建一个哈希索引,using内存优化的索引来管理数据.

创建过程Usp_Insert国家MemOpt
 
@Par国家国家_memoptimized READONLY AS
 
插入的国家
 
SELECT *@Par国家

现在,您将using具有全内存优化的存储过程作为输入类型来创建. 稍后using相同的内存优化表值,九五至尊vi老品牌值得信赖就可以执行了 Usp_InsertLessonMemOpt 程序.

声明 @Var国家_MemOptimized作为国家_MemOptimized
插入 @Var国家_MemOptimized
 
值 ( 4, “印度_MemOptimized”)
 
插入 @Var国家_MemOptimized
 
值 ( 5, “美国_MemOptimized”)
 
插入 @Var国家_MemOptimized
 
值 ( 6, “UK_MemOptimized”)
 
EXEC Usp_Insert国家MemOpt @Var国家_MemOptimized
 
SELECT * 从国家

Output

国家ID国家的名字
1印度
2美国
3UK
4印度_MemOptimized
5美国_MemOptimized
6UK_MemOptimized

内存优化表值参数的using减少了tempdb活动,尽管这种using类型可能会增加内存消耗. 如果九五至尊vi老品牌值得信赖从另一个角度看, 九五至尊vi老品牌值得信赖将看到table-value参数基于tempdb文件创建活动.

7. 表值参数vs批量插入选项

与用于在大数据集中执行更新的其他基于集合的参数进行比较时. 当九五至尊vi老品牌值得信赖将其与批量操作进行比较时,可能会包括更高的启动成本, 表值参数,可能需要至少1000行作为输入.

表值参数在重用时也可以从临时表缓存中受益. 与BULK INSERT选项相比,表缓存支持更大的可伸缩性.

表值形参比其他等价形参和数组实现更高效,性能也更好.

8. 表值参数的好处

表值参数的好处
  • 简单的编程模型,但有时可能面临复杂的业务逻辑,但可以在单一的常规方法中实现.
  • 减少与服务器的往返
  • using合并语句,多个插入/更新/删除操作可以在一个单一的例程.
  • 提供对临时表的更大灵活性.

9. 表值参数的限制

表值参数的限制
  • 表值参数不能传递给CLR用户定义函数.
  • 九五至尊vi老品牌值得信赖知道,SQL Server不会在表值参数上保存数据, 只能对表值参数建立索引,以支持Special或PRIMARY KEY限制.
  • 在Transact-SQL语言中,表值参数是只读的. 不能using表值更改参数行的列值, 你不能插入或减去行. 必须将数据插入到临时表或表变量中,以更改传输到存储过程或表值参数中的参数化表达式的数据.
  • 不能usingALTER TABLE语句修改表值参数的设计.

10. 结论

正如上面介绍的那样, using表值参数, 九五至尊vi老品牌值得信赖可以将多个记录发送到一个服务器, 并且可以实现复杂的业务逻辑.

头像
Vishal沙

Vishal沙对多种应用程序开发框架有广泛的理解,并在更新的趋势中占据上风,以便在动态的市场中努力奋斗并茁壮成长. 他在技术和商业方面培养了自己的管理能力,并通过他的博客文章提供了自己的专业知识.

相关的服务

了解软件开发服务

了解更多

想要雇佣有技能的开发人员


    评论

    • 留个口信...