SQL Server中的临时表和表变量 Declare @Tablename Table【转】

winson

　　我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.
　　临时表
　　临时表存储在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>
　　select * from ls_Qs_notes where>　　---返回记录998，行执行时间6589
　　M2：

　　select * from ls_Qs_notes where>　　---返回记录998 ，行执行时间60