听说临时表

Question

老听大家说临时表!
那么为什么要建临时表呢!建临时表有什么好处吗!临时表和表变量有有什么区别!!

Answer 1

临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。 
临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。 
例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。1、局部临时表（#开头）只对当前连接有效，当前连接断开时自动删除。   
2、全局临时表（##开头）对其它连接也有效，在当前连接和其他访问过它的连接都断开时自动删除。   
3、不管局部临时表还是全局临时表，只要连接有访问权限，都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。   
使用全局临时表需要加上   
if object_id('tempdb..##临时表') is not null 
drop table ##临时表 
else 
creeate table ##临时表..个人观点: 
临时表缺省使用硬盘，一般来说速度比较慢，那是不是就不用临时表呢？也不是，在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用TEMPDB的空间，这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间，然后使用临时表。 

Answer 2

临时表是指在tempdb中创建的表，分为局部临时表和全局临时表。
1、局部临时表的范围为创建时的连接，因此它不能在别的连接中被调用。即使有很多人都用同样的表名来创建临时表，也不会产生冲突，其原理在于系统会为每个用户的临时表加一个不同的后缀。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中。对局部临时表来说，它在执行存储过程中被创建，在存储过程执行完成后被删除，我们可以用如下代码来验证：
CREATE PROCEDURE Test
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT TestCol = x FROM #t
if   object_id('tempdb..#t')   is   not   null   
      print 'exist'
  else     
      print 'not exist'
GO
EXEC Test
GO
2、实现全局临时表功能的另一种方法是在tempdb中创建标准的用户表，姑且称为全局标准临时表（我没有查到官方的名称）。
名称                           命名方式                  范围                          删除时刻
局部临时表               以“#”开头              当前会话                  当前会话结束 
全局临时表               以“##”开头           所有会话                  所有引用会话结束
全局标准临时表       不以“#”开头         所有会话                  重启SQL Server
3、表变量类似于临时表，但它是存在于内存中。数据量实在大的时候，就应该使用临时表。 =================================================================================创建临时表
       方法一：
     create table #临时表名(字段1 约束条件,
                       字段2 约束条件,
                   .....)
         create table ##临时表名(字段1 约束条件,
                           字段2 约束条件,
                       .....)
         方法二：
     select * into #临时表名 from 你的表;
       select * into ##临时表名 from 你的表;
注：以上的#代表局部临时表，##代表全局临时表查询临时表
     select * from #临时表名;
       select * from ##临时表名;删除临时表
     drop table #临时表名;
       drop table ##临时表名;
drop table #Tmp   --删除临时表#Tmp
create table #Tmp --创建临时表#Tmp
(
    ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
    WokNo                varchar(50),   
    primary key (ID)      --定义ID为临时表#Tmp的主键      
);
Select * from #Tmp    --查询临时表的数据
truncate table #Tmp --清空临时表的所有数据和约束相关例子：Declare @Wokno Varchar(500) --用来记录职工号
Declare @Str NVarchar(4000) --用来存放查询语句
Declare @Count int --求出总记录数      
Declare @i int
Set @i = 0 
Select @Count = Count(Distinct(Wokno)) from #Tmp
While @i < @Count 
    Begin
       Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'
       Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output
       Select @WokNo,@i --一行一行把职工号显示出来
       Set @i = @i + 1
    End
临时表
可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所有会话中都可见。
本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)。SQL 语句使用 CREATE TABLE 语句中为 table_name 指定的名称引用临时表：CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)如 果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建，则 SQL Server 必须能够区分由不同用户创建的表。为此，SQL Server 在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临时表，其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀，为本地临时表指定的表名 table_name 不能超过 116 个字符。除非使用 DROP TABLE 语句显式除去临时表，否则临时表将在退出其作用域时由系统自动除去：当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表。
所有其它本地临时表在当前会话结束时自动除去。
全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全局临时表的会话结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动除去此表。 
在存储过程或触发器中创建的本地临时表与在调用存储过程或触发器之前创建的同名临时表不同。如果查询引用临时表，而同时有两个同名的临时表，则不定义针对 哪个表解析该查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。嵌套存储过程中对表名的所有引用都被解释为是针对该嵌套过程 所创建的表，例如：CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO下面是结果集：(1 row(s) affected)Test1Col    
----------- 
1          (1 row(s) affected)Test2Col    
----------- 
2          当 创建本地或全局临时表时，CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其它所有约束定义。如果在临时表中指定 FOREIGN KEY 约束，该语句将返回警告信息，指出此约束已被忽略，表仍会创建，但不具有 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。考虑使用表变量而不使用临时表。当需要在临时表上显式地创建索引时，或多个存储过程或函数需要使用表值时，临时表很有用。通常，表变量提供更有效的查询处理。

Answer 3

仅供参考：临时表最大好处是作为一个中间表来使用的，可以把一些复杂的查询结果放到里面。
再调用，会比较方便，速度也会比较快。
而且，不同的用户可以建名称相同的临时表（不是全局临时表）不冲突，用完后可以直接删除。临时表和表变量有有什么区别？？
1、定义方式不同：
临时表建表和普通表相同，不过前面要加#.
表变量这样定义：declare @aa table(aa int ,bb varchar(10))
前者放在tempdb数据库中，后者放在内存中。