这里我们在SQL Server 2005\SQL Server 2008版本上通过举例子，说明临时表和表变量两者的一些特征，让我们对临时表和表变量有进一步的认识。在本章中，我们将从下面几个方面去进行描述，对其中的一些特征举例子说明：
约束(Constraint) 索引(Index) I/0开销 作用域(scope) 存儲位置 其他
[b]例子描述 [/b]
[b]约束(Constraint)[/b]
在临时表和表变量，都可以创建Constraint。针对表变量，只有定义时能加Constraint。
e.g.在Microsoft SQL Server Management Studio（MSSMS）查询中，创建临时表并建Constraint场景，<脚本S1.>
< 脚本S1.>中，可以看出在临时表#1的创建时，创建Constraint如“Constraint PK_#1_ID Primary Key(ID)”，也可以在创建临时表#1后创建Constraint，如“Alter Table #1 Add Constraint CK_#1_Nr Check(Nr Between '10001' And'19999')”，下面我们来看表变量的场景，在定义表变量时不能指定Constraint名，定义表变量后不能对表变量创建Constraint。
e.g. 在定义表变量时不能指定Constraint名<代码S2.>
%E2%80%9D@1”的错误提示。
临时表与表变量不同，临时表的作用域是当前会话都有效，一直到会话结束或者临时表被Drop的时候。也就是说可以跨当前会话的几个批处理范围。
e.g.< 脚本S10.>
Use tempdb
go
if object_id('Tempdb..#1') Is Not Null
Drop Table #1
Go
Create Table #1
(
ID int,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate()),
Constraint PK_#1_ID Primary Key (ID)
)
Select * from #1
go --批处理结束点
Select * from #1
[url=http://images.cnblogs.com/cnblogs_com/wghao/201111/201111020056497132.png][img]http://files.jb51.net/file_images/article/201111/201111020056506967.png[/img]
[/url]
< 脚本S10.>中可以看出在”GO”前后都可以查询到临时表#1。
在描述临时表与表变量的作用域时，[i]有个地方要注意的是，当 sp_executesql 或 Execute 语句执行字符串时，字符串将作为它的自包含批处理执行. 如果表变量在sp_executesql 或 Execute 语句之前定义，在sp_executesql 或 Execute 语句的字符串中无法调用外部定义的表变量。[/i]
e.g.< 脚本S11.>
use tempdb
go
Set nocount on
declare @1 Table(
ID int primary key clustered,
Nr nvarchar(50) unique Nonclustered
)
Insert into @1 (id,Nr) values(1,'10001')
Insert into @1 (id,Nr) values(2,'10002')
Insert into @1 (id,Nr) values(8,'10003')
Insert into @1 (id,Nr) values(3,'10004')
Insert into @1 (id,Nr) values(7,'10005')
Select * From @1
Execute(N'Select * From @1')
go
[url=http://images.cnblogs.com/cnblogs_com/wghao/201111/201111020056514392.png][img]http://files.jb51.net/file_images/article/201111/201111020056533638.png[/img]
[/url]
< 脚本S11.>中，当执行到”Execute(N'Select * From @1')”时候，同样发生与< 脚本S9.>一样的错误提示“必须声明变量@1”.
临时表是可以在sp_executesql 或 Execute 语句执行字符串中被调用。这里不再举例子，如果你有所模糊可以参考< 脚本S11.>把表变量转成临时表测试下就能加深理解与记忆。
[b]存儲位置[/b]
说到临时表和表变量的存储位置，我们可以看到有很多版本的说法，特别是表变量。有的说表变量数据存储在内存中，有的说存储在数据库tempdb中，有的说有部分存储在内存，部分存储在数据库tempdb中。根据我查到的官方资料，说的是在SQL Server 2000下:
“[i]A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).[/i] ”
在SQL Server 2005\SQL2008的版本，表变量存储与临时表有相似，都会在数据库tempdb创建，使用到tempdb存储空间。
e.g.< 脚本S12.>临时表
use tempdb
go
Set nocount on
Exec sp_spaceused /*插入数据之前*/
if object_id('#1') Is not null
Drop Table #1
create table #1(ID int ,Nr nvarchar(50))
Insert into #1 (ID,Nr)
Select top(1) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b
Select top(1) name,object_id,type,create_date from sys.tables Order by create_date Desc
Exec sp_spaceused /*插入数据之后*/
Go
[url=http://images.cnblogs.com/cnblogs_com/wghao/201111/20111102005654682.png][img]http://files.jb51.net/file_images/article/201111/201111020056586731.png[/img]
[/url]
在< 脚本S12.>执行后，我们可以看到在数据库tempdb中的表sys.tables创建有表#1。我们接着看空间的使用情况，插入数据之前，数据库未使用空间(unallocated space)为510.39MB,向临时表#1插入1条数据后，数据库未使用空间为501.38MB,未使用空间变小了。再来看整个数据库的数据(data)使用的空间变化,从552KB变成560KB,使用了一页的数据空间(8kb)。这说明一点，临时表，即使你只插入一条数据都会使用到数据库tempdb的空间。也许会有人问，要是我只建临时表#1,不插入数据，会如何。我们可以结果：
[url=http://images.cnblogs.com/cnblogs_com/wghao/201111/201111020056592139.png][img]http://files.jb51.net/file_images/article/201111/20111102005703106.png[/img]
[/url]
这里你会发现前后的空间大小不变，不过，不要认为没有使用到数据库tempdb数据空间，当你多用户创建临时表结构的时候，你就会发现其实都会应用到数据库tempdb的空间。我这里创建了10个#1后的效果如：
[url=http://images.cnblogs.com/cnblogs_com/wghao/201111/201111020057039168.png][img]http://files.jb51.net/file_images/article/201111/20111102005705955.png[/img]
[/url]
相同的原理，我们使用类似的方法测试表变量的情况，发现结论是与临时表的一致的，会使用到数据库tempdb的空间.
e.g.< 脚本S13.>表变量
use tempdb
go
Set nocount on
Exec sp_spaceused /*插入数据之前*/
Declare @1 table(ID int ,Nr nvarchar(50))
Insert into @1 (ID,Nr)
Select top(1) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b
Select top(1) name,object_id,type,create_date from sys.objects Where type='U' Order by create_date Desc
Exec sp_spaceused /*插入数据之后*/
Go
Exec sp_spaceused /*Go之后*/
[url=http://images.cnblogs.com/cnblogs_com/wghao/201111/20111102005710983.png][img]http://files.jb51.net/file_images/article/201111/201111020057144490.png[/img]
[/url]
< 脚本S13.>中，我多写了一个”GO”之后检查空间大小的存储过程sp_spaceused。这样为了了更能体现表变量使用空间变化情况。从插入数据前和插入数据后的结果图来看，表变量不仅在数据库tempdb创建了表结构#267ABA7A类似的这样表，表变量也应用到了数据库tempdb的空间。不过这里注意一点就是在”Go”之后，我们发现表变量@1,会马上释放所使用的数据空间。为了更能体现使用空间情况。我们可以向表变量@1插入大量数据看空间变化情况（测试插入1000万的数据行）。
e.g.< 脚本S14.>
use tempdb
go
Set nocount on
Exec sp_spaceused /*插入数据之前*/
Declare @1 table(ID int ,Nr nvarchar(50))
Insert into @1 (ID,Nr)
Select top(10000000) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b
Select top(1) name,object_id,type,create_date from sys.objects Where type='U' Order by create_date Desc
Exec sp_spaceused /*插入数据之后*/
Go
Exec sp_spaceused /*Go之后*/
[url=http://images.cnblogs.com/cnblogs_com/wghao/201111/201111020057195914.png][img]http://files.jb51.net/file_images/article/201111/201111020057239007.png[/img]
[/url]
这里我们可清晰的看到数据库tempdb的大小(database_size)变化情况，从插入数据前的552.75MB变成插入数据之后的892.75MB。非常有意思的是我们在”Go之后”发现数据库大小保存在892.75MB,但数据使用空间(data)从560KB—>851464KB—>536KB ，说明SQL Server自动释放为使用的数据空间，但不会马上自动释放数据库分配的磁盘空间。我们在实际的环境中，发现临时数据库tempdb使用的磁盘空间越来越大，这是其中的原因之一。
[b]其他[/b]
临时表与表变量，还有其他的特征，如临时表受事务回滚，而表变量不受事务回滚影响。对应事务方面，更为正确的说法是表变量的事务只在表变量更新期间存在。因此减少了表变量对锁定和记录资源的需求。
e.g.< 脚本S15.>
use tempdb
go
Set nocount on
if object_id('#1') Is not null
Drop Table #1
create table #1(ID int ,Nr nvarchar(50))
Declare @1 table(ID int ,Nr nvarchar(50))
begin tran /*开始事务*/
Insert into #1 (ID,Nr)
Select top(1) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b
Insert into @1 (ID,Nr)
Select top(1) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b
rollback tran /*回滚事务*/
Select * from #1
Select * from @1
Go
[url=http://images.cnblogs.com/cnblogs_com/wghao/201111/20111102005724544.png][img]http://files.jb51.net/file_images/article/201111/201111020057271675.png[/img]
[/url]
这里发现”Rollback Tran”之后，临时表#1没有数据插入，而表变量@1还有一条数据存在。说明表变量不受”Rollback Tran”所影响。它的行为有类似于局部变量一样。
另外SQL Server对表变量不保留任何的统计信息，因为如此，我们在数据量大的时候使用表变量，发现比临时表要慢许多。前面在I/O开销那里我们取有一个特殊的例子，这里不再举例。
[b]小结[/b]
无论如何，临时表和表变量有各自的特征，有自己优点和缺点。在不同的场景选择它们灵活应用。本文章是我对临时表和表变量的一些认识理解，可能有些地方说的不够好或者遗漏，你可以留言或Email与我联系，我会继续改进或纠正，我也不希望有些错误的见解会误导别人。正如[url=http://ask.sqlservercentral.com/users/19/phil-factor/]Phil Factor[/url]说的一句" I'd hate to think of anyone being misled by my advice!".
[b]附参考：[/b]
[url=http://support.microsoft.com/kb/305977/en-us]http://support.microsoft.com/kb/305977/en-us[/url]
[url=http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server]http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server[/url]
[url=http://msdn.microsoft.com/en-us/library/aa175774%28SQL.80%29.aspx]http://msdn.microsoft.com/en-us/library/aa175774(SQL.80).aspx[/url]
[url=http://msdn.microsoft.com/en-us/library/cc966545.aspx]http://msdn.microsoft.com/en-us/library/cc966545.aspx[/url]
[url=http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/]http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/[/url]
[url=http://support.microsoft.com/kb/942661/en-us]http://support.microsoft.com/kb/942661/en-us[/url]
机械节能产品生产企业官网模板...
大气智能家居家具装修装饰类企业通用网站模板...
礼品公司网站模板
宽屏简约大气婚纱摄影影楼模板...
蓝白WAP手机综合医院类整站源码（独立后台）...
