[SQL]临时表和表变量

baacloud免费翻墙vpn注册使用

我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.

临时表
局部临时表
全局临时表
表变量

                              
临时表
临 时表存储在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

 
表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏