SQL中的临时表和表变量.docx
《SQL中的临时表和表变量.docx》由会员分享，可在线阅读，更多相关《SQL中的临时表和表变量.docx（12页珍藏版）》请在冰点文库上搜索。
SQL中的临时表和表变量
SQL中的临时表和表变量
SQL2008-11-2122:
05:
28阅读210评论0 字号：
大中小 订阅
我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.
临时表
局部临时表
全局临时表
表变量
临时表
临时表存储在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
表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.
在开发过程中，经常会遇到使用表变量和本地临时表的情况。
下面是对二者的一个介绍：
1.为什么要使用表变量
表变量是从2000开始引入的，微软认为与本地临时表相比，表变量具有如下优点：
a.与其他变量的定义一样，表变量具有良好的定义范围，并会被自动清除；
b.在存储过程中使用表变量会减少存储过程重新编译的发生；
c.表变量需要更少的锁请求和日志资源；
d.可以在表变量上使用UDF,UDDT,XML。
2.表变量的限制
与临时表相比，表变量存在着如下缺点：
a.在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据很多的情况下，会导致查询优化器选择很差的执行计划；
b.不能直接在表变量上创建索引，但可以通过创建约束（主键、唯一）来建立索引；
c.在DECLARE后，不能再对表变量进行更改；
d.不能对表变量执行INSERTEXEC,SELECTINTO语句；
e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句，但如果表变量是在动态SQL语句内定义的，则可以。
3.那什么时候可以使用表变量
要使用表变量应该根据如下规则来判断：
a.表的行数；
b.使用表变量能够减少的重新编译次数；
c.查询的类型和对索引或者统计信息的依赖程度；
d.需要生用UDF,UDDT,XML的时候。
其实也就说，得从实际出发，根据具体的查询，作出具体的选择。
但是，其中很关键的一点，如果表的行数非常多，使用表变量其实是更费资源的。
有人提出了这样的建议：
对于行数较少的情况下（小于1000行）可以使用表变量；如果行数很多（有几万行），则使用临时表。
因此，在实际的开发中，应通过分别使用临时表或表变量进行对比后，才作出决定。
下面是一个例子，插入临时表和表变量的数据有20多万行，可以看到，使用临时表的时间是使用表变量所花时间的1/5。
表'SalesOrderHeader'。
扫描计数3，逻辑读取130次，物理读取9次，预读43次，lob逻辑读取0次，lob物理读取0次，lob预读0次。
表'#SalesOrderDetail___________________________________________________________________________________________________00000000001F'。
扫描计数3，逻辑读取12331次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。
表'Worktable'。
扫描计数0，逻辑读取0次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。
SQLServer执行时间:
CPU时间=2281毫秒，占用时间=19726毫秒。
selectwithtemporarytable:
20140ms
********************************************************************************
表'SalesOrderHeader'。
扫描计数0，逻辑读取764850次，物理读取17次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。
表'#4E88ABD4'。
扫描计数1，逻辑读取12331次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。
SQLServer执行时间:
CPU时间=4375毫秒，占用时间=107160毫秒。
selectwithtablevariable:
107160ms
4.使用表变量的误区
对于表变量，很多人认为，表变量和其他变量一样，只存在内存中，其实这是不正确的，表变量也存在tempdb中。
可以通过下面例子进行对比。
CREATETABLE#TempTable（TT_Col1INT）
DECLARE@TableVariableTABLE（TV_Col1INT）
SELECTTOP2*
FROMtempdb.sys.objects
ORDERBYcreate_dateDESC
name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#03317E3D
#TempTable__________________________________________________________________________________________________________000000000003
#03317E3D就是刚创建的表变量；
5.其他
表变量不受rollback影响，某些情况下会破坏数据的完整性。
CREATETABLE#TempTable（TT_Col1INT）
DECLARE@TableVariableTABLE（TV_Col1INT）
INSERT#TempTableVALUES
（1）
INSERT@TableVariableVALUES
（1）
BEGINTRANSACTION
INSERT#TempTableVALUES
（2）
INSERT@TableVariableVALUES
（2）
ROLLBACK
SELECT*FROM#TempTable
/*
TT_Col1
-------
1
*/
SELECT*FROM@TableVariable
--返回了两条记录
/*
TV_Col1
-------
1
2
*/
6.参考
升级会员 