欢迎来到.net学习网

欢迎联系站长一起更新本网站!QQ:879621940

您当前所在位置:首页 » SQLServer教程 » 正文

CTE,临时表,表变量区别跟具体适用场景分析

创建时间:2013年12月20日 15:08  阅读次数:(5923)
分享到:
在这之前我们先了解CTE,临时表,表变量的基本概念
一、临时表:临时表有两种类型:本地表和全局表。在与首次创建或引用表时相同的 SQL Server 实例连接期间,本地临时表只对于创建者是可见的。当用户与 SQL Server 实例断开连接后,将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的,当引用该表的所有用户都与 SQL Server 实例断开连接后,将删除全局临时表。

二、CTE:CTE(Common Table Expression) ,即公用表表达式,可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE ⅥEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次。

三、表变量:说白了和表没什么区别,只不过是以变量的形式存在,你像访问普通的表一样去访问,去做增删改查的操作。最重要的是为什么有了临时表,还要整个表变量呢,存在即合理,表变量确实有其优点,例如它不需要在存储过程结束的时候释放(DROP),导致存储过程编译更少等等。

CTE相对比较简单一点,
非递归的CTE从物理来说,有点类似普通的视图的概念,在编译的时候优化器会把它扩展开来。 比如下面的,QO会把1转化为2
1,WITH O_CTE ([name] , [object_id] )
AS
(SELECT [name] , [object_id] FROM [sys].[all_objects])
SELECT top 11 O.[name] , O.[object_id]
FROM O_CTE O INNER JOIN [sys].[all_columns] C ON O.[object_id]=C.[object_id] ;
GO

-- transformation 
2,SELECT top 11 O.[name] , O.[object_id]
FROM ( SELECT [name] , [object_id] FROM [sys].[all_objects])O INNER JOIN [sys].[all_columns] C ON O.[object_id]=C.[object_id] ;
GO

表变量与临时表很复杂,所以误解相当的多,首先说一下相关的误解的东西。

1),存储位置.
一句话:表变量与临时表在存储位置上没有本质区别。

2,表变量与临时表跟tempdb关系。
表变量与临时表在tempdb 实例化,并且最初在BPOOL中产生,并且在checkpoint发生之前,它们就已经存在于tempdb 中了,虽然它还没有刷入磁盘,但是它们就已经属于并且存在于tempdb中了。 当然在checkpoint后生之后,BPOOL中的数据被输入磁盘,但是不管有没有发生checkpoint,数据有没有被输入磁盘,换句话说,无论表变量与临时表只存是在BPOOL中 ,还是同时存在于BPOOL与磁盘(manual checkpoint),或者只存在于磁盘(lazy writer后),表变量与临时表都存在于tempdb中。

上面的描述同样适用于用户数据库中的用户表,就好像是在用户数据库USERDB中用户表usertable生成1000条记录,那它肯定是首先在BPOOL中生成的,但是在checkpoint之前,它也是不会被刷入磁盘的,那这个1000条记录属于数据库USERDB吗?当然拉。 

3,表变量不参与事物吗?那为什么还要产生日志呢?
表变量并非不参与事物,而不参与用户事物,系统事物还是参与的,所以会产生日志。

4),表变量不参与锁机制吗?
表变量当然会参与锁机制。
DBCC TRACEOFF(1200,-1)
GO
DECLARE @TV TABLE (CL int)
DBCC TRACEON(1200,-1,3604)
INSERT INTO @TV (CL) VALUES (1)
DBCC TRACEOFF(1200,-1)
GO

5,表变量会不会重编译,而临时表会重编译吗?
当然不是,临时表的重编译是需要条件的。
比如下面的,你执行多次Proc1,但是你只会看到一次的SP:Recompile,这说话,临时表的计划被重用了。
create procedure Proc1
as 
begin
create table #t1(a int, b int); 
insert into #t1 values(1,2);
select * from #t1;
end

exec Proc1

再没有达到某些限定的阀值之前,是不需要重编译的,具体公式如下。
If n < 6, Recompilation threshold = 6.
If 6 <= n <= 500, Recompilation threshold = 500.
If n  > 500, Recompilation threshold = 500 + 0.20 * n.

另外在执行计划中, Estimated Rowcount是一个非常重要的东西,因为它直接决定了执行计划的选择,下面来问大家一些问题,后面会有问题的答案,大家可以先想想再看答案。

----------------------------------------------------------------------------------------
问题
----------------------------------------------------------------------------------------
先创建表并插入数据
SET NOCOUNT ON;

DECLARE @tb TABLE (
[C1] [nvarchar](50) NOT NULL,
[C2] [date] NOT NULL,
[C3] [nchar](1) NOT NULL,
[C4] bit NULL,
[C5] bit NOT NULL);

INSERT INTO @tb
SELECT '1',getdate(),'1','1','1'

--生成1024条测试记录
declare @i int
set @i=0
while @i<10
begin
INSERT INTO @tb
SELECT * from @tb
set @i=@i+1
end

SET STATISTICS PROFILE ON;
SELECT* from @tb 
--1.Estimated Rowcount为1,这个很明显。
SELECT* from @tb OPTION(RECOMPILE)
--为什么 Estimated Rowcount为1024?

SELECT* from @tb where [C1] like '1' OPTION(RECOMPILE) 
--为什么 Estimated Rowcount为102.4?

SELECT* from @tb where [C1] Not like '1' OPTION(RECOMPILE) 
--为什么 Estimated Rowcount为921.6?

SELECT* from @tb where [C1] between '1' and '2' OPTION(RECOMPILE) 
--为什么 Estimated Rowcount为92.16?

SELECT * from @tb where [C1] not between '1' and '2' OPTION(RECOMPILE) 
--为什么 Estimated Rowcount为522.24?

SELECT* from @tb where [C4]=1 OPTION(RECOMPILE)
--为什么 Estimated Rowcount为337.92?

SELECT* from @tb where [C5]=1 OPTION(RECOMPILE)
--为什么 Estimated Rowcount为512?

SELECT* from @tb where [C2]< getdate() OPTION(RECOMPILE)
--为什么 Estimated Rowcount为307.2?

SET STATISTICS PROFILE OFF;

----------------------------------------------------------------------------------------
答案
----------------------------------------------------------------------------------------
RECOMPILE使得@tb在运行insert into之后重新评估@tb的行数,因为这个时候已经insert完毕,所以能准确的得到它的行数。
因为总共插入了1024行,所以@tb的total count 为1024"。

SELECT* from @tb OPTION(RECOMPILE)
--为什么 Estimated Rowcount为1024?
因为总共就插入了1024行,插入之后再次评估它的行数,就能得它比较准确的值。

SELECT* from @tb where [C1] like '1' OPTION(RECOMPILE) 
--为什么 Estimated Rowcount为102.4?
对于不模糊的like,Estimated Rowcount=total count *10%=1024*10%=102.4

Estimated RowcountSELECT* from @tb where [C1] Not like '1' OPTION(RECOMPILE) 
--为什么 Estimated Rowcount为921.6?
Not like 的 Estimated Rowcount=total count -like Estimated Rowcount=1024-102.4=921.6

SELECT* from @tb where [C1] between '1' and '2' OPTION(RECOMPILE) 
--为什么 Estimated Rowcount为92.16?
对于between 的Estimated Rowcount=total count *9%=1024*9%=92.16

SELECT * from @tb where [C1] not between '1' and '2' OPTION(RECOMPILE) 
--为什么 Estimated Rowcount为522.24?
not between 的Estimated Rowcount为total count *51%=522.24

SELECT* from @tb where [C5]=1 OPTION(RECOMPILE)
--为什么 Estimated Rowcount为512?
[C5]的类型为 bit NOT NULL,所以有2种值,所以Estimated Rowcount=total count *1/2=512

SELECT* from @tb where [C4]=1 OPTION(RECOMPILE)
--为什么 Estimated Rowcount为337.92?
[C4]的类型为 bit NULL,所以有3种值的可能,所以Estimated Rowcount=total count *1/3=337.92

SELECT* from @tb where [C2]< getdate() OPTION(RECOMPILE)
--为什么 Estimated Rowcount为307.2?
一般对于 >或者<Estimated Rowcount为 Estimated Rowcount*30%=307.2p
来源:http://bbs.csdn.net/topics/390667246
说明:所有来源为 .net学习网的文章均为原创,如有转载,请在转载处标注本页地址,谢谢!
【编辑:Wyf

打赏

取消

感谢您的支持,我会做的更好!

扫码支持
扫码打赏,您说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

最新评论

共有评论0条
  • 暂无任何评论,请留下您对本文章的看法,共同参入讨论!
发表评论:
留言人:
内  容:
请输入问题 63+25=? 的结果(结果是:88)
结  果: