局部临时表VS全局临时表

http://blog.csdn.net/wu_qionglei/article/details/5927042
http://www.cnblogs.com/pinbo/articles/1681673.html

临时表
SQL Server 支持临时表。临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表，SQL Server 将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库 tempdb 内。

临时表有两种类型：

本地临时表
以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表。

全局临时表
以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表即被除去。
--应用本地临时表
declare @sql varchar(100)
set @sql='select ''123'' as a,''abasdf'' as b into #t'
exec(@sql)
select * from #t
go
/*发现不能正确显示临时表的数据
提示：
服务器: 消息 208，级别 16，状态 1，行 5
对象名 '#t' 无效。
*/

--修改为全局临时表
declare @sql varchar(100)
set @sql='select ''123'' as a,''abasdf'' as b into ##t'
exec(@sql)
select * from ##t
drop table ##t
go
/*可以正确显示临时表的数据
分析原因:exec(@sql) 相当于重新建立一个数据库对话，所以对于本地临时表，使用exec(@sql)创建临时表，
在exec外部不能正确访问；而使用全局临时表就ok了
*/

SQL code

    exec ('select top 10 * into ##temp from syhouse'); exec ('select * from ##temp');






==============================================================


表变量： 

  DECLARE @tb  table(id   int   identity(1,1), name   varchar(100)) 
  INSERT @tb 

  SELECT id, name
  FROM mytable

  WHERE name like ‘zhang%’



临时表：

  SELECT name, address
  INTO #ta   FROM mytable
  WHERE name like ‘zhang%’



表变量和临时表的比较：

    临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。
    表变量缺省放在内存，速度快，所以在触发器，存储过程里如果数据量不大，应该用表变量。
    临时表缺省使用硬盘，一般来说速度比较慢，那是不是就不用临时表呢？也不是，在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用 TEMPDB的空间，这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间，然后使用临时表。
    临时表相对而言表变量主要是多了I/O时间，但少了对内存资源的占用。数据量较大的时候，由于对内存资源的消耗较少，使用临时表比表变量有更好的性能。
    建议：触发器、自定义函数用表变量；存储过程看情况，大部分用表变量；特殊的应用，大数据量的场合用临时表。
    表变量有明确的作用域，在定义表变量的函数、存储过程或批处理结束时，会自动清除表变量。
    在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
    涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
    表变量需要事先知道表结构，普通临时表，只在当前会话中可用与表变量相同into一下就可以了,方便；全局临时表：可在多个会话中使用存在于temp中需显示的drop。（不知道表结构情况下临时表方便一些）
    全局临时表的功能是表变量没法达到的。
    表变量不必删除，也就不会有命名冲突，临时表特别是全局临时表用的时候必须解决命名冲突。
    应避免频繁创建和删除临时表，减少系统表资源的消耗。
    在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
    如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
    如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
    慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。