我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.
临时表 临时表存储在TempDB数据库中,所有的使用此SQL Server 实例的用户都共享这个TempDB,因为我们应该确保用来存储TempDB数据库的硬盘有足够的空间,以使之能够自己的增长.最好能够存储在一个拥有独立硬盘控制器上.因为这样不存在和其它的硬盘I/O进行争用. 我们很多程序员认为临时表非常危险,因为临时表有可能被多个连接所共享.其实在SQL Server中存在两种临时表:局部临时表和全局临时表,局部临时表(Local temp table)以#前缀来标识,并且只能被创建它的连接所使用.全局临时表(Global temp table)以##前缀来进行标识,并且可以和其它连接所共享. 局部临时表 局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中自动为局部临时表的表名后面加上了一个唯一字符来标识.如: CREATE TABLE [#DimCustomer_test] ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) 现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表#DimCustomer_test已经被加上了后缀: USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’ the Result is: name #DimCustomer_test___________________________________________________________________________________________________000000000005 全局临时表 下面我们来看一下全局临时表: CREATE TABLE [##DimCustomer_test] ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) 现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表##DimCustomer_test没有被加上了后缀: USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’ The Result are: #DimCustomer_test___________________________________________________________________________________________________000000000005 ##DimCustomer_test --Drop test temp tables DROP TABLE [##DimCustomer_test] DROP TABLE [#DimCustomer_test] 可以看到我们刚才创建的全局临时表名字并没有被加上标识. 表变量 表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些! 另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.如: USE AdventureWorksDW GO DECLARE @DimCustomer_test TABLE ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) ---insert data to @DimCustomer_test INSERT @DimCustomer_test ( [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] ) SELECT [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] FROM DimCustomer SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity) FROM @DimCustomer_test INNER JOIN FactInternetSales ON @DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey Group BY CustomerKey Result: Server: Msg 137, Level 15, State 2, Line 32 Must declare the variable ’@DimCustomer_test’. 如果我们对上面的查询进行更改,对查询使用别名(并且找开IO): -----in the follow script,we used the table alias. DECLARE @DimCustomer_test TABLE ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) ) INSERT @DimCustomer_test ( [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] ) SELECT [CustomerKey] , [FirstName] ,[MiddleName] ,[LastName] FROM DimCustomer SELECT t.CustomerKey,f.OrderQuantity FROM @DimCustomer_test t INNER JOIN FactInternetSales f ON t.CustomerKey = f.CustomerKey where t.CustomerKey=13513 表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除. ---------------------------------------- 另外在今天帮同事Tuning SQL 脚本地时候，发现对于大数据量表的查询（10w-100W），用变量的方式比用select 的方式居然执行时间减少了100倍！！似懂非懂，但从来没有想到差别如此大，惊讶ing，记录一笔，研究一下 M1： declare @tempID int set @tempID =(select lots_id from qs_notes where id='CVT20080321') select * from ls_Qs_notes where id = @tempID ---返回记录998，行执行时间6589 M2： select * from ls_Qs_notes where id =(select lots_id from qs_notes where id='CVT20080321') ---返回记录998 ，行执行时间60
结论 综上所述，大家会发现临时表和表变量在底层处理机制上是有很多差别的。 简单地总结，我们对于较小的临时计算用数据集推荐使用表变量。如果数据集比较大，如果在代码中用于临时计算，同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化，比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等)，也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。 一般对于大的数据集我们推荐使用临时表，同时创建索引，或者通过SQL Server的统计数据(Statisitcs)自动创建和维护功能来提供访问SQL语句的优化。如果需要在多个用户会话间交换数据，当然临时表就是唯一的选择了。需要提及的是，由于临时表存放在tempdb中，因此要注意tempdb的调优。SQL中的临时表和表变量