临时表与表变量

今天用到了临时表与表变量，在网上查了些资料，也查了MSDN，写个工作记日，以后可能用得着。

临时表

临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。

临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。



临时表存储在TempDB数据库中,所有的使用此SQL Server 实例的用户都共享这个TempDB,因为我们应该确保用来存储TempDB数据库的硬盘有足够的空间,以使之能够自己的增长.最好能够存储在一个拥有独立硬盘控制器上.因为这样不存在和其它的硬盘I/O进行争用.

局部临时表

局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中自动为局部临时表的表名后面加上了一个唯一字符来标识.如:

CREATE TABLE [#temp_test]
(
[id] [int],[name] [nvarchar](50),[age] int
)


CREATE TABLE [##temp_test]
(
[id] [int],[name] [nvarchar](50),[age] int
)


USE TempDB
GO


SELECT name FROM sysobjects WHERE name LIKE '%temp_test%'


table变量有以下优点：   
  1、table变量的行为类似于局部变量，有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。   
  在其作用域内，table变量可像常规表那样使用。该变量可应用于SELECT、INSERT、UPDATE和DELETE语句中用到表或表的表达式的地方。但是，table不能用在下列语句中： 
  INSERT INTO table_variable EXEC 存储过程。 
  SELECT select_list INTO table_variable语句。 
  在定义table变量的函数、存储过程或批处理结束时，自动清除table变量。
  2、在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
  3、涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。


1.表变量在内存中，临时表存放在硬盘上； 
2.用临时表要考虑锁不锁表的问题； 
3.数据量太大应该用临时表。