SQL临时表

IF object_id('tempdb..#temp') is not null

   drop table #temp

else

    print '不存在!'

 

一：创建临时表

1： select * into #temp from A

2：

创建临时表

create table #temp

(

Tradecount int,

ID int

)

对临时表新增数据

insert into #tempexec sp_executesql @sql

 

注：对于第一种创建，不用指明临时表有哪些字段。这种方式简单 ，最主要是不用指明字段类型和一一对应字段

对于第二种创建，必须先创建临时表，再新增。现对于第一种麻烦。但是这种方式可以直接获取存储过程（动态执行语句）的数据。

 

二：

1、局部临时表（#开头）只对当前连接有效，当前连接断开时自动删除。或者可以理解为是当前会话有效

2、全局临时表（##开头）对其它连接也有效，在当前连接和其他访问过它的连接都断开时自动删除。   

3、不管局部临时表还是全局临时表，只要连接有访问权限，都可以用drop   table   #Tmp(或者drop   table   ##Tmp)来显式删除临时表。   

 

三：临时表和表变量的区别：

with table1 as

(

select * from persons where age < 30

)

select * from table1 -- 使用了名为table1的公共表表达式

 

 

备注：如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。

区别：

　　1)表变量是存储在内存中的，当用户在访问表变量的时候，SQL Server是不产生日志的，而在临时表中是产生日志的;

　　2)在表变量中，是不允许有非聚集索引的;

　　3)表变量是不允许有DEFAULT默认值，也不允许有约束;

　　4)临时表上的统计信息是健全而可靠的，但是表变量上的统计信息是不可靠的;

　　5)临时表中是有锁的机制，而表变量中就没有锁的机制。

　　临时表和表变量的选择：

　　1)使用表变量主要需要考虑的就是应用程序对内存的压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。我们对于较小的数据或者是通过计算出来的推荐使用表变量。如果数据的结果比较大，在代码中用于临时计算，在选取的时候没有什么分组的聚合，就可以考虑使用表变量。

　　2)一般对于大的数据结果，或者因为统计出来的数据为了便于更好的优化，我们就推荐使用临时表，同时还可以创建索引，由于临时表是存放在Tempdb中，一般默认分配的空间很少，需要对tempdb进行调优，增大其存储的空间。

　　CTE和WITH AS短语结合使用提高SQL查询性能：

　　cet要比表变量效率高得多！

　　表变量实际上使用了临时表，从而增加了额外的I/O开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况。

 

注：

• 使用临时表（create table #Temp）存储处理复杂的关系的结果集
• 使用临时表时，用小型数据量的小表来限制性能影响。（数据量过大不适合使用临时表，会超耗资源的。并且不会起到优化的效果，反而更慢）
• 如果临时表中使用inner join , group by , order by 或 where，要确保临时表有索引。
•