临时表和表变量

临时表

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