临时表和表变量

临时表

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

 

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

要想将存储过程返回的表保存到临时表或者表变量中，必须先对他们定义，

临时表：

Create   table  #mytmptbl(
col1  nvarchar ( 100 ))

insert   into  #mytmptbl
exec  dbo.GetCustomerID  ''
select   *   from  #mytmptbl

drop   table  #mytmptbl

表变量

declare   @tvar   table
( col1  nvarchar ( 100 ))

insert   into   @tvar
exec  dbo.GetCustomerID  ''
select   *   from   @tvar