合 MSSQL中的临时表
Tags： MSSQLSQL Servertempdb临时表
概述
在sql server里临时表存储在TempDB库中，TempDB是一个系统数据库，它只有Simple恢复模式，也是最小日志记录操作。主要用于存放局部临时表，全局临时表，表变量，都是基于临时特征，每次服务器或服务重启后，都会按照Model库的配置重新创建TempDB库。
在sql server 2012中TempDB表可以配置在故障转移中。
在TempDB库中存放三类对象包括：用户对象, 内部对象, 行版本存储。
TempDB库只有一个文件组，就是primary文件组，增加其它文件组会提示报错。在权限方面，所有用户默认都具有访问TempDB库权限。
Tempdb 的物理属性
下表列出了 tempdb 数据和日志文件的初始配置值。 对于不同版本的 SQL Server，这些文件的大小可能略有不同。
| 文件 | 逻辑名称 | 物理名称 | 文件增长 |
|---|---|---|---|
| 主数据 | tempdev | tempdb.mdf | 按 10% 自动增长，直到磁盘已满 |
| 日志 | templog | templog.ldf | 以 10% 的速度自动增长到最大 2 TB |
使用Tempdb的作用
(1) 通过tempdb库可以缓存临时表和表变量，用于减少数据表的查询次数以及锁问题。缓存允许删除和创建临时对象的操作能快速地执行，并减少页分配的争用问题。
(2) 分配页闩锁协议得到改善。 从而减少使用的 UP（更新）闩锁数。
(3) 减少了 tempdb 的日志开销。 从而减少了 tempdb 日志文件上的磁盘 I/O 带宽消耗。
(4) 分配混合的页中的算法tempdb得到了改进。
使用tempdb的限制, 不能对 tempdb 数据库执行以下操作：
(1)添加文件组。
(2)备份或还原数据库。
(3)更改排序规则。 默认排序规则为服务器排序规则。
(4)更改数据库所有者。 tempdb 的所有者是 sa。
(5)创建数据库快照。
(6)删除数据库。
(7)从数据库中删除 guest 用户。
(8)启用变更数据捕获。
(9)参与数据库镜像。
(10)删除主文件组、主数据文件或日志文件。
(11)重命名数据库或主文件组。
(12)运行 DBCC CHECKALLOC。
(13)运行 DBCC CHECKCATALOG。
(14)将数据库设置为 OFFLINE。
TempDB存储的三类对象
在SQL 2005以后，引入了一张新的管理视图：sys.dm_db_file_space_usage。通过查询这张视图，能了解tempdb的空间使用情况，能知道tempdb的空间是被哪一块对象使用掉的，是用户对象(user_object_reserved_page_count字段)，还是系统对象（internal_object_reserved_page_count字段），还是版本存储区（version_store_reserved_page_count字段。在了解这个dmv视图之前，先了解tempdb库的三类对象。
用户临时对象(user_object_reserved_page_count)
由用户会话显示创建的对象，可以在系统表目录中找到，用户临时对象主要包括以下内容：
(a) 创建临时表和索引，不过这些表在重启后清空。
(b) 全局临时表以 ##开头的表。适用于所有会话操作该表信息。需要显式的删除或重启服务才能清除表。
