关闭

临时表、表变量、CTE的比较

3225人阅读 评论(10) 收藏 举报
分类:

 1、临时表

临时表包括:以#开头的局部临时表,以##开头的全局临时表。

 

a、存储

不管是局部临时表,还是全局临时表,都会放存放在tempdb数据库中。

 

b、作用域

局部临时表:对当前连接有效,只在创建它的存储过度、批处理、动态语句中有效,类似于C语言中局部变量的作用域。

全局临时表:在所有连接对它都结束引用时,会被删除,对创建者来说,断开连接就是结束引用;对非创建者,不再引用就是结束引用。

但最好在用完后,就通过drop  table 语句删除,及时释放资源。

 

c、特性

与普通的表一样,能定义约束,能创建索引,最关键的是有数据分布的统计信息,这样有利于优化器做出正确的执行计划,但同时它的开销和普通的表一样,一般适合数据量较大的情况。

有一个非常方便的select ... into 的用法,这也是一个特点。

  

 

2、表变量

a、存储

表变量存放在tempdb数据库中。

 

b、作用域

和普通的变量一样,在定义表变量的存储过程、批处理、动态语句、函数结束时,会自动清除。

 

c、特性

可以有主键,但不能直接创建索引,也没有任何数据的统计信息。

SQL Server是以表变量的数据在上千条前提,来生成执行计划的,所以表变量适合数据量相对较小的情况。

必须要注意的是,表变量不受事务的约束,下面的例子说明了这一点:

declare @tb table(v int primary key,vv varchar(10))

begin tran
	insert into @tb
	select 1,'aa'
rollback tran


--虽然上面回滚了事务,但还是会返回1条记录
select * from @tb


begin tran
	update @tb
	set vv= 'bb'
	where v = 1
rollback tran


--返回的数据显示,update操作成功,根本没有回滚
select * from @tb

 

3、CTE

CTE,就是通用表表达式。

 

a、存储

产生的数据一般存储在内存,不会持久化存储。

也可以持久化:

;with cte
as
(
select 1 as v,'aa' as vv
union all
select 2,'bb'
)

--把cte的数据存储在tb_cte表
select * into tb_cte
from cte

select * from tb_cte;


--运用cte,删除数据
;with cte_delete
as
(
select * from tb_cte
)

delete from cte_delete where V = 1

--返回1条数据,另一条已删除
select * from tb_cte


当然,在实际运行时,有些部分,比如假脱机,会把数据存储在tempdb的worktable、workfile中,另外,一些大的hash join和排序操作,也会把中间数据存储在tempdba。

 

b、作用域

只存在于当前的语句。

 

c、特性

在同一个语句中,一次定义,可以多次引用。另外,可以定义递归语句,不过这个递归语句的性能,还不如写个while循环来的好。

 

之前在上家公司开发报表时,大量使用了CTE,一个CTE中包含了10到20个的语句,最后关联出结果集。

SQL Server在生成执行计划时,会对每个小语句,根据引用语句中相关表的统计信息,估计产生多少行结果,然后再估计这些小语句产生的多个结果集,当再次进行关联时,估计会有多少行结果集,也就是对估计的结果,再次进行估计。

这样偏差就会越来越大,最终往往会导致产生的执行计划不够准确,这样往往会有性能问题。

 

其实,本质问题就是,一个语句几千行,语句太复杂了,SQL Server很难做出最优化的执行计划,这确实难为SQL Server了,所以后来就把这个CTE改为,每一小段语句,把结果集通过select into插入到临时表中,因为临时表是有统计信息的,这样最后关联多个临时表。

对SQL Server而言,现在有了每个小的结果集的精确的统计信息,那么就自然能做出更为精确的执行计划,执行性能自然上升。

 

5
0
查看评论

[SQLServer] 临时表、表变量、 CTE

本文原本是为了说明游标在某些特定环境下的用途,突然发现用来说明临时表、表变量和 CTE 的关系更为合理一些。 本文的例子给了一串数字,是为了求得同组中所有数字的乘积,包括累计叠加等等,这样的案例通过一般的分组方式很难办到,而通过游标更容易实现。 创建一个<a onclick="fun...
  • xiaoxu0123
  • xiaoxu0123
  • 2010-03-19 23:59
  • 501

比较表变量和临时表

1、回滚事务对表变量无效,回滚事务对临时表有效SET NOCOUNT ON; DECLARE @TestTable TABLE ( RowID INT IDENTITY PRIMARY KEY CLUSTERED, Name VARCHAR(9) NOT NULL UNIQUE...
  • roy_88
  • roy_88
  • 2013-01-03 19:13
  • 3994

SQL Server 表变量和临时表系列之概念篇

问题引入 “菜鸟啊,最近我看到阿里云开发者论坛的数据库RDS中有人在提SQL Server表变量和临时表如何选择的问题,你去深入探讨下这个问题吧,解答解答他们的疑惑吧”,老鸟又开始为菜鸟找活干了。 “鸟哥啊,关于表变量和临时表使用选择的问题啊,向来行业里争论不休,我比较担心我们的观点被人家拍...
  • lishimin1012
  • lishimin1012
  • 2017-01-06 14:37
  • 543

SQL SERVER临时表的使用&表变量的使用

临时表: 创建临时表可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表在所有会话中都可见。 本地临时表的名称前面有一个编号符 (#table_name),而全局临时表的名称前面有两个编号符 (##table_name)。
  • S630730701
  • S630730701
  • 2016-08-03 16:57
  • 2448

SQL Server 表变量与临时表区别

问题 1:为什么在已经有了临时表的情况下还要引入表变量?解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server 联机丛书“表”(Table) 一文中所述,表变量(如局部变量)具有明确定义的范围,在该范围结束时会自...
  • lovehongyun
  • lovehongyun
  • 2008-01-09 09:27
  • 10989

Sql表变量和临时表

我们在数据库中使用表的时候,经常会遇到两种使用表的方法,分别就是使用临时表及表变量。在实际使用的时候,我们如何灵活的在存储过程中运用它们,虽然它们实现的功能基本上是一样的,可如何在一个存储过程中有时候去使用临时表而不使用表变量,有时候去使用表变量而不使用临时表呢?   临时表   临时表...
  • liwei825755184
  • liwei825755184
  • 2016-03-29 10:15
  • 1428

临时表vs.表变量以及它们对SQLServer性能的影响

 问题 1:为什么在已经有了临时表的情况下还要引入表变量?解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server 联机丛书“表”(Table) 一文中所述,表变量(如局部变量)具有明确定义的范围,在该范...
  • lainY7mail
  • lainY7mail
  • 2008-04-17 12:30
  • 1902

创建临时表,大数据的临时表与小数据量的表变量

1.  将数据倒入表中的方法: 已经存在的表:select * into #tempMail from MailLog where ActionFlag = N   不存在的表:create table #Result (  Tv...
  • lishuaide0517
  • lishuaide0517
  • 2010-04-07 10:32
  • 484

MySQL中的临时表使用方法

当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后多这些表运行查询。   当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个...
  • JimyJimang
  • JimyJimang
  • 2012-02-21 18:06
  • 1060

一些不常用的T-sql(临时表,用变量表示表名,判断表,字段是否存在,sql启动job

--创建本地临时表#tb --当#tb存在就删除#tb,不存在就新建 if object_id('tempdb..#tb') is not null drop table #tb else create table #tb (字段名 类型, 字段名2 类型2, ……) ...
  • xxhysj
  • xxhysj
  • 2012-11-24 09:40
  • 2228
    个人资料
    • 访问:526369次
    • 积分:9035
    • 等级:
    • 排名:第2474名
    • 原创:362篇
    • 转载:14篇
    • 译文:1篇
    • 评论:137条
    博客专栏
    最新评论