1、临时表

和普通的表一样，能定义约束，能创建索引，最关键的是有数据分布的统计信息，这样有利于优化器做出正确的执行计划，但同时它的开销和普通的表一样，一般适合数据量较大的情况。有一个非常方便的select ... into 的用法，这也是一个特点。

 

使用场景：

 

数据量小直接当做中间表使用，数据量较大可以通过优化提高查询效率，对于复杂的查询可以将中间结果放在临时表中以固化执行计划（专治执行计划走错）

 

 

2、表变量

 

2.1 存储

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

 

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

 

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

 

2.4 使用场景：小数据量（百条以内） 注意：表变量不受事务的约束，下面的DEMO会演示。 

--DEMO 表变量
declare @tb table(col1 int primary key,col2 varchar(10))  
   
begin tran  
    insert into @tb  
    select 1,'aa' 
rollback tran  
     
--虽然上面回滚了事务,但还是会返回1条记录  
select * from @tb  
   
begin tran  
    update @tb  
    set col2= 'bb'
where col1 = 1  
rollback tran  
   
--返回的数据显示,update操作成功,根本没有回滚  
select * from @tb

 

3、CTE
3.1 内涵 
CTE，就是通用表表达式。
 
3.2 存储
产生的数据一般存储在内存，不会持久化存储。
也可以持久化：

;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和排序操作，也会把中间数据存储在tempdb。

  

CTE下第一条SQL

递归，SQL逻辑化（重复的部分写到CTE里面，能减少SQL量，增加SQL条理性和可读性） 注意：SQL逻辑化改写并不能固定执行计划（逻辑中间表，实际解析后还是一个SQL）

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

 

CTE递归案例 

--目的：通过传入ParentId(=5)，返回该记录的所有子节点数据
IF OBJECT_ID('DiGui','U') IS NOT NULL DROP TABLE DiGui
CREATE TABLE DiGui(
    Id INT,
    ParentId int
    )
INSERT INTO dbo.DiGui
        ( Id, ParentId )
select 4    ,0
union select 5    ,0
union select 7    ,0
union select 2    ,1
union select 8    ,5
union select 15    ,5
union select 9    ,7
union select 14    ,11
union select 30    ,15
union select 23    ,15
union select 41    ,18
union select 104,    23
union select 42    ,30
union select 39    ,30
union select 53    ,39
union select 67    ,39
union select 88    ,39
union select 107,    39

;with temp ( [Id], [parentid])
as
(
select Id, parentid FROM DiGui WHERE [parentid] = 5
union all
select a.Id, a.parentid
from DiGui a
inner join temp b ON a.[parentid] = b.[Id]
)
select * from temp