临时表与表变量
今天用到了临时表与表变量,在网上查了些资料,也查了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.数据量太大应该用临时表。