分类： 数据库开发技术
2009-04-01 20:44:11
上篇文章,我们对tempdb系统数据库有了一个新的认识,现在我们来慢慢对其进行深入的了解和掌握。
首先，先介绍“tempdb的空间使用”，有下面几种类型的对象会占用tempdb的空间：
. 内部对象
. 版本存储
. 用户对象
内部对象
内部对象是指由SQL Server系统内部自动创建的一些系统级别的对象，应用程序不能直接的从这些对象删除或者插入数据，系统对象的元数据存储在内存里面，并且不能通过象“sys.all_objects”这样的系统目录来进行查询，因此，我们可认为内部对象是一些隐藏对象。通常系统对象被用于：
. 存储排序的中间临时结果集。
. 存储HASH连接以及HASH聚合的中间结果集。
. 存储XML变量或者象LOB这样的大对象类型变量。LOB数据类型包括：text, image, ntext, varchar(max), varbinary(max)以及其他。
. 存储会利用到Spool运算符(如CTE查询)保存的中间结果集。
. 存储键集驱动游标生成的工作表。
. 通过静态游标存储查询结果。
. 通过ServiceBroker存储传输中的消息。
. 通过INSTEAD OF触发器存储数据以及内部的处理。
内部对象还可以用于下面这些的任何一个功能。比如，DBCC CHECK内部使用的查询需要利用到Spool运算符生成中间结果集，查询通知，以及事件通知，ServiceBroker，因此它们同样需要利用到tempdb的空间。
更新内部对象不会产生日志纪录。除非涉及排序单位，否则内部对象上页分配是不会生成日志纪录的。如果语句失败，这些对象将会被释放。每个tempdb上的内部对象至少占用9个页（包括1个IAM页和8个数据页的区）。
版本存储
版本存储用于存储新特性中通过事务产生的行版本。例如快照隔离级别，触发器，MARS（多个活动的结果集）以及联机索引创建。在SQL Server 2005中，有两个版本的存储区。关于更多的相关信息，请参见联机帮助：
版本存储由append-only存储单位构成。对于连续的插入和随机的查找，append-only存储单位是非常高效的。它们不会显示在系统目录，比如sys.all_objects。插入版本存储不会产生日志纪录。每个单位可以存储很多行的版本。如果有新的版本需要被存储，大约每1分钟就有1个新的存储单位被创建。
由于版本存储涉及的内容比较多且复杂，在这里就不再一一论述，更多内容，请网友们自行参考联机丛书关于“版本存储”的章节。
用户对象
可以在系统目录sys.all_objects找到用户对象。Sp_spaceused可以显示这些对象占用的空间。用户对象包括用户定义的表和索引以及系统表和索引。这两种类型的表在磁盘上的数据结构都完全一样。可以通过T-SQL语句对用户表进行操作，不能通过T-SQL语句直接的对系统表进行修改，它们对于使用系统目录是可见的。通常用户对象上的操作是可以被纪录日志的，包括BCP，BULK INSERT，SELECT INTO以及索引重建操作，这些行为和其他的简单恢复模型的数据库的行为是完全一样的。
用户定义的表包括全局临时表例如##t，以及局部临时表例如#t.全局临时表会在其依赖的整个会话过期或者中止以后释放，而局部临时表只存在于其依赖的某种作用域。在另外一方面，也会伴随一个作用域的结束而被释放。（例如存储过程）局部临时表包括表变量@t,表值函数的返回值，以及那些使用了带有sort_in_tempdb选项的联机聚集索引创建所需要的Mapping索引。
对于临时表的作用域，网友们经常遇到下面的这些问题，顺便在这里提出来：
1．
我们先看下面的代码：
use tempdb
go
create proc p1
as
set nocount on
select top 1 * into #t from sys.objects
select * from #t
go
我们创建了个存储过程，先通过系统表插入1行纪录生成一个局部临时表#t,然后再通过SELECT语句查询这个临时表。
然后，我们创建完以后，通过执行:
exec p1
go
我们可以看到结果集，但如果，我们再次通过SELECT 查询语句对#t进行查询：
select * from #t
go
我们将会看到下面的错误信息，这是因为在存储过程的作用域完之后，这个局部临时表对象也随之被销毁了。
Msg 208, Level 16, State 0, Line 1
Invalid object name '#t'.
如果想在执行完存储过程以后，还可以进行SELECT查询，把局部临时表替换为全局临时表就可以了。
2．
还是利用上面提到的存储过程p1
create proc p1
as
set nocount on
select top 1 * into #t from sys.objects
select * from #t
go
突然某天，需求改变，创建临时表的源表需要改变或者需要适应可变的需求，那么我们肯定会考虑传入一个表名参数，使用动态语句来实现，代码如下：
alter proc p1
@table_name varchar(100)
as
set nocount on
declare @sql varchar(200)
set @sql = 'select top 1 * into #t from '+@table_name
exec(@sql)
select * from #t
go
然后我们执行存储过程：
exec p1 @table_name='sys.objects'
我们并未得到我们想要的结果，而是一个错误信息：
Msg 208, Level 16, State 0, Procedure p1, Line 9
Invalid object name '#t'.
这也是因为，这里的局部临时表只存在于动态语句内的作用域，对于这样的需求我们可以通过下面两个方式来解决：
第一种方式是通过将查询语句一并包含到动态语句里，代码如下：
alter proc p1
@table_name varchar(100)
as
set nocount on
declare @sql varchar(200)
set @sql = 'select top 1 * into #t from '+@table_name + '
select * from #t'
exec(@sql)
go
第二种方式则是将局部临时表修改为全局临时表##t。
3．
我们来考虑全局临时表的作用域，我们做个测试，这个测试还是利用到最开始的那个存储过程P1，只不过代码我们把局部临时表调整为了全局临时表：
alter proc p1
as
set nocount on
select top 1 * into ##t from sys.objects
select * from ##t
go
我们创建完这个存储过程以后，先在查询窗口1执行1次这个存储过程，那么按照我们之前说的，则会创建一个全局的的临时表，我们可以在执行完存储过程以后，依然可以使用SELECT语句对其进行查询。
然后我们试着重新开启第2，第3个……查询窗口，同样执行语句查询：
select * from ##t
我们依然可以看到我们想要的结果集。我们再次把查询窗口1关闭，然后再次在窗口2，3……执行上面这个查询代码，结果出错了。
通过这个测试，说明了什么，大家都已经很清楚了。
最后，谢谢各位看完这篇文章，下一次，我将介绍SQL Server 2005中tempdb的改进以及tempdb的磁盘空间需求，欢迎各位捧场，谢谢大家。
