SQL Server 中的本地和全局临时表

Question

SQL Server 中的本地临时表和全局临时表有什么区别？

Answer 1

• 表变量 (DECLARE @t TABLE) 仅对创建它的连接可见，并在批处理或存储过程结束时被删除。

• 本地临时表 (CREATE TABLE #t) 仅对
  创建它的连接，并在连接被删除时删除
  已关闭。

• 全局临时表 (CREATE TABLE ##t) 对所有人都可见，
  并在引用它们的所有连接都关闭时被删除。

• Tempdb 永久表（USE tempdb CREATE TABLE t）对以下对象可见
  每个人，并且在服务器重新启动时被删除。

Answer 2

我发现这个解释非常清楚（它是来自 Technet 的纯副本）：

临时表有两种类型：本地临时表和全局临时表。本地临时表仅在首次创建或引用表时与 SQL Server 实例的同一连接期间对其创建者可见。用户与 SQL Server 实例断开连接后，本地临时表将被删除。全局临时表在创建后对任何用户和任何连接都可见，并且在引用该表的所有用户与 SQL Server 实例断开连接时被删除。

Answer 3

1.) 本地临时表仅在连接期间存在，或者如果在复合语句内定义，则在复合语句期间存在。

本地临时表仅适用于 SQL Server 会话或
创建表的连接（意味着单个用户）。这些都是
当创建表的会话已自动删除
已关闭。本地临时表名以单个哈希开头
(“#”) 符号。

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Name','Address');
GO
Select * from #LocalTemp

本地临时表的范围存在于当前会话中
user 表示当前查询窗口。如果您要关闭当前
查询窗口或打开一个新的查询窗口并会尝试在上面查找
创建了临时表，它会给你错误。

---

2.) 全局临时表永久保留在数据库中，但行仅存在于给定连接中。当连接关闭时，全局临时表中的数据消失。但是，表定义仍保留在数据库中，以便下次打开数据库时进行访问。

全局临时表可用于所有 SQL Server 会话或
连接（指所有用户）。这些可以通过任何 SQL 创建
服务器连接用户和所有这些都会被自动删除
SQL Server 连接已关闭。全局临时表
名称以双井号（“##”）开头。

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Name','Address');
GO
Select * from ##GlobalTemp

全局临时表对所有 SQL Server 连接可见
而本地临时表仅对当前 SQL Server 可见
连接。

Answer 4

引用在线书籍：

本地临时表仅在当前会话中可见；全局临时表对所有会话都可见。

临时表超出范围时会自动删除，除非使用 DROP TABLE 显式删除：

• 在存储过程中创建的本地临时表会在存储过程完成时自动删除。该表可以被创建该表的存储过程所执行的任何嵌套存储过程引用。调用创建该表的存储过程的进程无法引用该表。
• 所有其他本地临时表都会在当前会话结束时自动删除。
• 当创建表的会话结束并且所有其他任务都停止引用全局临时表时，全局临时表将自动删除。任务和表之间的关联仅在单个 Transact-SQL 语句的生命周期内维护。这意味着在创建会话结束时主动引用该表的最后一个 Transact-SQL 语句完成后，全局临时表将被删除。

Answer 5

我没有看到任何答案向用户显示我们可以在哪里找到全局临时表。在 SSMS 中导航时，您可以在同一位置查看本地和全局临时表。下面的屏幕截图取自此链接。

数据库 -->系统数据库 -->临时数据库 -->临时表

Answer 6

本地临时表：如果您创建本地临时表，然后打开另一个连接并尝试查询，您将收到以下错误。

临时表只能在创建它们的会话中访问。

全局临时表：
有时，您可能想要创建一个可供其他连接访问的临时表。在这种情况下，您可以使用全局临时表。

仅当所有引用全局临时表的会话都关闭时，全局临时表才会被销毁。

Answer 7

值得一提的是，还有： 数据库范围的全局临时表（目前仅受 Azure SQL 数据库支持）。

SQL Server 的全局临时表（以 ## 表名启动）存储在 tempdb 中，并在整个 SQL Server 实例的所有用户会话之间共享。

Azure SQL 数据库支持也存储在 tempdb 中且范围仅限于数据库级别的全局临时表。这意味着全局临时表由同一 Azure SQL 数据库中的所有用户会话共享。 来自其他数据库的用户会话无法访问全局临时表。

-- 会话 A 在 Azure SQL 数据库 testdb1 中创建全局临时表 ##test
-- 并添加 1 行
创建表##test(a int, b int);
INSERT INTO ##测试值(1,1);

-- 会话 B 连接到 Azure SQL 数据库 testdb1 
-- 并且可以访问会话A创建的表##test
从##test中选择*
 - -结果
1,1

-- 会话 C 连接到 Azure SQL 数据库 testdb2 中的另一个数据库 
-- 并且想要访问在 testdb1 中创建的 ##test。
-- 由于全局临时表的数据库范围，此选择失败 
从##test中选择*
 - -结果
消息 208，级别 16，状态 0，第 1 行
无效的对象名称“##test”

---

更改数据库范围配置

GLOBAL_TEMPORARY_TABLE_AUTODROP = { 开 |离开 }

适用于：Azure SQL 数据库（功能处于公共预览版）

允许设置全局临时的自动删除功能
表。默认为ON，表示全局临时
当任何会话不使用表时，表将被自动删除。 什么时候
设置为OFF，需要显式删除全局临时表
使用 DROP TABLE 语句或将自动删除
服务器重新启动。

对于 Azure SQL 数据库单一数据库和弹性池，可以在 SQL 数据库的各个用户数据库中设置此选项
服务器。
在 SQL Server 和 Azure SQL 数据库托管实例中，此选项在 TempDB 中设置，并且各个用户数据库的设置已
没有效果。