临时表

临时表详解

create table #temp(id int)
insert into #temp(id) values(1)
insert into #temp(id) values(2)
select * from #tempCode language: CSS (css)

临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。

本地临时表

本地临时表就是用户在创建表的时候添加了 # 前缀的表，其特点是根据数据库连接独立。只有创建本地临时表的数据库连接有表的访问权限，其它连接不能访问该表。不同的数据库连接中，创建的本地临时表虽然”名字”相同，但是这些表之间相互并不存在任何关系。在 SQL Server 中，通过特别的命名机制保证本地临时表在数据库连接上的独立性。

真正的临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的 I/O 次数，因此也提高了系统效率。临时表在事务完毕或会话完毕数据自动清空，不必记得用完后删除数据。

比如在一个新建查询（链接1）先创建一个临时表然后马上查询：

CREATE TABLE #Temp
(
    id int,
    customer_name nvarchar(50),
    age int
)
INSERT INTO #Temp VALUES(1,'春运',12)
SELECT * FROM #TempCode language: PHP (php)

但是在另外一个新建查询（链接2）执行：

SELECT * FROM #TempCode language: CSS (css)

报错如下：

消息 208，级别 16，状态 0，第 1 行
对象名 '#Temp' 无效。Code language: JavaScript (javascript)

删除临时表：

DROP TABLE #TempCode language: CSS (css)

全局临时表

全局临时表的名称以两个数字符号（##）打头，创建后对任何数据库连接都是可见的，当所有引用该表的数据库连接从 SQL Server 断开时被删除。

例如在一个数据库连接中用如下语句创建全局临时表 ##Temp，然后插入三行数据：

CREATE TABLE ##Temp
(
    id int,
    customer_name nvarchar(50),
    age int
)
INSERT INTO ##Temp VALUES(1,'春运',12)
SELECT * FROM ##TempCode language: PHP (php)

然后我们再开一个链接（链接2）：

SELECT * FROM ##TempCode language: CSS (css)

但是如果此时关闭上面的链接1，再执行链接2 就报下面错误了。

这是因为关闭数据库连接1后，数据库连接2此时也没有语句正在使用临时表 ##Temp，所以 SQL Server 认为此时已经没有数据库连接在引用全局临时表 ##Temp 了，就将 ##Temp 释放掉了。

如果我们在链接2加一个事务和排他锁：

BEGIN TRAN
SELECT * FROM ##Temp WITH(XLOCK)Code language: CSS (css)

结果显示，尽管关闭了数据库连接1，但由于数据库连接2在事务中一直持有全局临时表 ##Temp 的排他锁（X锁），所以临时表 ##Temp 并没有随着数据库连接1的关闭而被释放掉。只要数据库连接2中启动的事务没有被回滚或提交，那么数据库连接2会一直持有临时表 ##Temp 的排他锁，这时 SQL Server 会认为还有数据库连接正在引用全局临时表 ##Temp，所以 ##Temp 不会被释放掉。