sqlserver TempDB详解

本人使用的测试数据库是2012版本的

TempDB就是临时数据库。它具有以下特性：

1、重启服务之后，没有东西会存储在TempDB中，因为每次重启都会重建。隐含的意思就是你不可以对这个库进行备份还原。

2、它永远是简单恢复模式。

3、它只是一个文件组，就是Primary文件组，不能增加额外的文件组，但是可以增加文件。

4、它主要存放3类对象：用户对象、内部对象、版本存储。

下面介绍一下这三种对象

一、用户对象

这种临时对象主要包括：全局临时表及上面的索引、局部临时表及上面的索引、表变量，这些可能是在会话中产生或存储过程中产的。这里重点介绍一下临时表和表变量的区别：

1、统计信息

表变量不会创建统计信息，统计信息要么为0，要么为1。优化器永远会把表变量当作里面只有一条数据或者没有数据的表来对待。开启实际执行计划，下面我们来做一个测试：

表变量：

declare @TableVar table
(
	c1 int
)
insert into @TableVar
select top 1000000 ROW_NUMBER() over(order by t1.number) as N
from master..spt_values t1
cross join master..spt_values t2;

select count(*) from @TableVar

实际执行计划：

临时表：

create table #TempTable
(
	c1 int
)
insert into #TempTable
select top 1000000 ROW_NUMBER() over(order by t1.number) as N
from master..spt_values t1
cross join master..spt_values t2;

select count(*) from #TempTable

drop table #TempTable

主要查看预估行数(Estimated Number of Rows)，表变量是1，临时表是1000000

2、索引

很多人误以为表变量不能创建索引，其实不然。由于它是变量，索引需要定义，也正因为如此，索引需要预先定义。在定义了表变量以后，再使用就不能添加索引了。另外，表变量只能创建主键或唯一约束，但是，即使上面有主键或约束，也不会因此而具有统计信息，因为在编译的时候，这些变量是不存在的。所以也不会存在重编译。

修改上面脚本中表变量的定义，给列c1加上primary key。实际执行计划如下：

可以看到扫描变成了聚集索引扫描。其实更准确来说，表变量上面的是约束，而不是索引。

3、架构修改

架构修改可能出现在临时表上，但是不会出现在表变量上。架构修改会产生重编译，非预期的重编译并不是好事。

4、存放的地方

这是一直被误解的地方，很多人以为表变量存放在内存中，所以会比临时表要快，真的是这样吗。先重启一下SQL Server，然后执行以下语句：

select session_id,DB_NAME(database_id) as [数据库名],user_objects_alloc_page_count 
from sys.dm_db_session_space_usage where session_id>50

结果如下：

下面创建一个临时表，并插入一行数据：

create table #TempTable
(
	ID INT
)
insert into #TempTable values(1)

再次执行查询语句，结果如下：

下面创建一个表变量，并插入一行数据：

declare @TempTable table
(
	ID INT
)
insert into @TempTable values(1)

结果如下：

可以看到临时表和表变量都引起了TempDB上页分配。所以可以知道表变量也是创建在TempDB中的，而不是在内存中。

关于如何选择使用临时表还是表变量，给出如下建议：

对于大数据量，偏向于使用临时表；小数据量(一般来说小于100行)，则可以使用表变量。

原因：表变量没有统计信息，优化器对此只有进行扫描操作。

不过在Sqlserver2014之后，在创建表变量的时候可以使用一个参数，使表变量不使用临时数据库，完全在内存中操作。具体如何设置请参考MSDN。

2、内部临时对象

内部临时对象是在查询过程中存储临时数据的对象，如：Sorts、假脱机、Hash关联和游标等。

3、版本存储

从sqlserver 2005开始，出现了乐观并发模式，这类并发模式会借用TempDB来存放修改前的版本数据。每一行数据被修改前，都会在TempDB中创建一个相同的行，并加上14Bytes的长度。