【T-SQL系列】临时表、表变量
发布日期:2022-03-13 05:36:10 浏览次数:13 分类:技术文章

本文共 2519 字,大约阅读时间需要 8 分钟。

临时表

临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志,虽它们在Tempdb中体现,是分配在内存中的,它们也支持物理的磁盘,但用户在指定的磁盘里看不到文件。
临时表分为本地和全局两种,本地临时表的名称都是以“#”为前缀,只有在本地当前的用户连接中才是可见的,当用户从实例断开连接时被删除。全局临时表的名称都是以“##”为前缀,创建后对任何用户都是可见的,当所有引用该表的用户断开连接时被删除。
临时表可以创建索引,也可以定义统计数据,所以可以用数据定义语言(DDL)的声明来阻止临时表添加的限制,约束,并参照完整性,如主键和外键约束。
临时表在创建之后可以修改许多已定义的选项,包括:
1)添加、修改、删除列。例如,列的名称、长度、数据类型、精度、小数位数以及为空性均可进行修改,只是有一些限制而已。
2)可添加或删除主键和外键约束。
3)可添加或删除 UNIQUE 和 CHECK 约束及 DEFAULT 定义(对象)。
4)可使用 IDENTITY 或 ROWGUIDCOL 属性添加或删除标识符列。虽然 ROWGUIDCOL 属性也可添加至现有列或从现有列删除,但是任何时候在表中只能有一列可具有该属性。
5)表及表中所选定的列已注册为全文索引。
比较临时表及表变量都可以通过SQL的选择、插入、更新及删除语句,它们的的不同主要体现在以下这些:
  1)表变量是存储在内存中的,当用户在访问表变量的时候,SQL Server是不产生日志的,而在临时表中是产生日志的;
  2)在表变量中,是不允许有非聚集索引的;
  3)表变量是不允许有DEFAULT默认值,也不允许有约束;
  4)临时表上的统计信息是健全而可靠的,但是表变量上的统计信息是不可靠的;
  5)临时表中是有锁的机制,而表变量中就没有锁的机制。

表变量

基本原则:能用表变量就用表变量.实在不行才使用临时表
表变量主要是开销系统的内存,而临时表则使用tempdb.对于小数据量的中间数据存储,可以使用表变量,而当需要临时保存的数据很大时,建议使用临时表.
  表变量创建的语法类似于临时表,区别就在于创建的时候,必须要为之命名。表变量是变量的一种,表变量也分为本地及全局的两种,本地表变量的名称都是以“@”为前缀,只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀,一般都是系统的全局变量,像我们常用到的,如 @@Error代表错误的号,@@RowCount代表影响的行数。
1、为什么要使用表变量
表变量是从2000开始引入的,微软认为与本地临时表相比,表变量具有如下优点:
a.与其他变量的定义一样,表变量具有良好的定义范围,并会被自动清除;
b.在存储过程中使用表变量会减少存储过程重新编译的发生;
c.表变量需要更少的锁请求和日志资源;
d.可以在表变量上使用UDF,UDDT,XML。
2、表变量的限制
与临时表相比,表变量存在着如下缺点:
a.在表变量上没有统计信息,查询优化器根据固定的预估值来选择执行计划,在数据很多的情况下,会导致查询优化器选择很差的执行计划;
b.不能直接在表变量上创建索引,但可以通过创建约束(主键、唯一)来建立索引;
c.在DECLARE后,不能再对表变量进行更改;
d.不能对表变量执行INSERT EXEC , SELECT INTO语句(只针对05前的版本);
e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句,但如果表变量是在动态SQL语句内定义的,则可以。
3、那什么时候可以使用表变量
要使用表变量应该根据如下规则来判断:
a.表的行数;
b.使用表变量能够减少的重新编译次数;
c.查询的类型和对索引或者统计信息的依赖程度;
d.需要生用UDF,UDDT,XML的时候。
其实也就说,得从实际出发,根据具体的查询,作出具体的选择。但是,其中很关键的一点,如果表的行数非常多,使用表变量其实是更费资源的。有人提出了这样的建议:对于行数较少的情况下(小于1000行)可以使用表变量;如果行数很多(有几万行),则使用临时表。
因此,在实际的开发中,应通过分别使用临时表或表变量进行对比后,才作出决定。
4、使用表变量的误区
对于表变量,很多人认为,表变量和其他变量一样,只存在内存中,其实这是不正确的,表变量也存在tempdb中。可以通过下面例子进行对比。

CREATE TABLE #TempTable ( TT_Col1 INT )DECLARE @TableVariable TABLE ( TV_Col1 INT )SELECT TOP 2        *FROM    tempdb.sys.objectsORDER BY create_date DESCDROP TABLE #TempTable

 

5、其他

由于表变量作用域有限,并且不是持久数据库的一部分,因而不受事务回滚的影响。
表变量不受rollback影响,某些情况下会破坏数据的完整性。

CREATE TABLE #TempTable ( TT_Col1 INT )DECLARE @TableVariable TABLE ( TV_Col1 INT )INSERT  #TempTableVALUES  ( 1 )INSERT  @TableVariableVALUES  ( 1 )BEGIN TRANSACTION INSERT  #TempTableVALUES  ( 2 )INSERT  @TableVariableVALUES  ( 2 )ROLLBACK SELECT  *FROM    #TempTableSELECT  *FROM    @TableVariableDROP TABLE #TempTable

 

转载于:https://www.cnblogs.com/Bobby0322/p/4083924.html

转载地址:https://blog.csdn.net/weixin_30323961/article/details/98214189 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:Leetcode 453.最小移动次数使数组元素相等
下一篇:nginx解决跨域

发表评论

最新留言

很好
[***.229.124.182]2024年03月18日 12时35分13秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

php中的变量名称用什么表示,PHP变量,方法,类等名称中的有效字符是什么? 2019-04-21
pic32mx是什么cpu_PIC32MX单片机外设库使用(Ⅰ)- 系统时钟及I/O口基本设置 2019-04-21
用c 在mysql上存图片_C 批量保存图片进 mysql 利用MYSQL_BIND插入longblob 2019-04-21
mysql 1045 28000_mysql报关于用户密码1045(28000),几种处理方法 (zhuan) 2019-04-21
solr比mysql的优势_Solr与Elasticsearch的优缺点比较总结和归纳 2019-04-21
华为博士招聘上机考试题目_牛客网-华为-2020届校园招聘上机考试-3 2019-04-21
python中for可以做变量名吗_Python中使用动态变量名的方法 2019-04-21
mysql 日期转换天数_MySQL 日期操作 增减天数、时间转换、时间戳 2019-04-21
java对象去重复_JAVA中List对象去除重复值的方法 2019-04-21
java bss_[转] .bss段和.data段的区别 2019-04-21
java上传图片损坏_大神求助 上传图片后 图片损坏 2019-04-21
java socket唯一标识符_Java Socket编程之常识网络基础知识 2019-04-21
java给xyz大小排序_java递归实现string xyz排序 2019-04-21
arctime必须要java_Arctime使用教程 Arctime常见问题解答 2019-04-21
mysql pxc mysql5.7_mysql之PXC5.7.18集群系列——1. Percona XtraDB Cluster 搭建 2019-04-21
mysql 自适应字段宽度_box-sizing解决自适应布局容器宽度问题 2019-04-21
java 配置文件配置路径_Java读取配置文件路径设置 2019-04-21
vux 选择器_vue中的scoped分析以及在element-UI和vux中的应用 2019-04-21
java cache 有效期_springboot cache 自定义过期时间及自定义缓存key前缀 2019-04-21
java实验一目的_Java实验报告(实验一) 2019-04-21