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

Question

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

- andrew Sullivan

---

以下是关于此内容的一些摘要细节，点击这里。 - Jayesh Sorathia

在使用表变量时要小心。如果你在查询中使用它们，由于它们没有索引，可能会导致查询计划出现严重的性能问题。请注意避免。 - user1944539

实际上，如果需要的话，临时表是可以建立索引的，但这也需要时间和资源，因此仍然可能会导致性能或资源问题。 - Andrew Steitz

Answer 1

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

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

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

• Tempdb永久表 (USE tempdb CREATE TABLE t) 对所有人可见，并在服务器重新启动时被删除。

- Anthony Faull

---

值得指出的是：本地临时表在创建它们的作用域关闭时被删除。因此，如果你在一个存储过程内创建了一个本地临时表，然后尝试在该存储过程外部访问它 - 它将不存在。 - user111013

+1 给 Will。我试图使用本地临时表作为优化器，并尝试使用存储过程作为“如果不存在则创建和填充”的初始化程序。正如你所说，除非使用全局临时表，否则它不起作用。 - quillbreaker

“that have referenced them”指的是所有引用这些对象的连接都已关闭。如果一个连接#1中的StoredProc创建了一个##TempTable，那么在另一个连接#2（假设该连接在表创建时处于活动状态）10分钟后能否看到它？答案是：当创建这些全局临时表的会话结束并且所有其他任务停止引用它们时，这些表将自动删除。（更多详细信息请参见不同的回答页）。 - tbone

我尝试使用存储过程来创建后续逻辑所需的本地临时表(#t)，但结果发现父存储过程必须创建它们，以便它们可以在子存储过程调用中使用。这是一件令人沮丧的事情，因为我们有一堆存储过程必须以相同的方式设置表并调用公共存储过程。全局临时表是否适用于此类情况，其中子调用可以访问由兄弟创建的表？我们正在使用SQL Server 2008。 - Brandon

@Brandon，你说得很对。那确实是缺失的功能。TSQL对瞬态数据的适当作用域支持非常不完善。就好像语言设计者希望所有东西都是全局的一样。而且几乎没有闭包的支持。你可以传递一个游标变量。但这又是另一个问题，因为逐行处理是不可取的。 - Anthony Faull

Answer 2

我觉得这个解释非常清晰（它是从 Technet 纯复制过来的）：

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

- Don

---

非常好的、有用的回答！我正在寻找关于SQL Server何时自动清理全局临时表的具体信息。 - kwill

非常清晰简洁的回答。有人能想到全局临时表的一个好用例吗？一个能够说明它们与本地临时表目的不同的用例？ - Trevor

@Trevor：我有一个使用全局临时表的用例。我在一个临时表中收集了一些数据，并尝试通过EXEC msdb.dbo.sp_send_dbmail发送电子邮件...这只能使用全局临时表来实现。 - Michael W.

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

本地临时表的范围仅限于当前用户会话，即当前查询窗口。如果关闭当前查询窗口或打开新的查询窗口并尝试查找上述创建的临时表，则会出现错误。

---

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

全局临时表对所有 SQL Server 会话或连接（即所有用户）都可用。任何 SQL Server 连接用户都可以创建这些表，并且当所有 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 连接可见。

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

- Vivek S.

---

您对全局临时表的定义是我所期望的（来自其他数据库），但是我的测试显示SQL Server中实际发生的情况是：“当创建表的会话结束并且所有其他任务停止引用它们时，全局临时表会自动删除”。 - Nickolay

Answer 4

引用自 Books Online：

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

临时表在超出范围时会自动删除，除非使用 DROP TABLE 明确删除：

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

- Christian Hayter

Answer 5

我没有看到任何回答告诉用户在哪里可以找到全局临时表。当在SSMS中导航时，您可以在同一位置查看本地和全局临时表。以下截图来自此链接。

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

- Code Novice

Answer 6

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

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

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

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

- Reza Jenabi

Answer 7

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

Global temporary tables for SQL Server (initiated with ## table name) are stored in tempdb and shared among all users’ sessions across the whole SQL Server instance.

Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users’ sessions within the same Azure SQL Database. User sessions from other databases cannot access global temporary tables.

-- Session A creates a global temp table ##test in Azure SQL Database testdb1
-- and adds 1 row
CREATE TABLE ##test ( a int, b int);
INSERT INTO ##test values (1,1);

-- Session B connects to Azure SQL Database testdb1 
-- and can access table ##test created by session A
SELECT * FROM ##test
---Results
1,1

-- Session C connects to another database in Azure SQL Database testdb2 
-- and wants to access ##test created in testdb1.
-- This select fails due to the database scope for the global temp tables 
SELECT * FROM ##test
---Results
Msg 208, Level 16, State 0, Line 1
Invalid object name '##test'

---

ALTER DATABASE SCOPED CONFIGURATION

GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }

APPLIES TO: Azure SQL Database (feature is in public preview)

Allows setting the auto-drop functionality for global temporary tables. The default is ON, which means that the global temporary tables are automatically dropped when not in use by any session. When set to OFF, global temporary tables need to be explicitly dropped using a DROP TABLE statement or will be automatically dropped on server restart.

With Azure SQL Database single databases and elastic pools, this option can be set in the individual user databases of the SQL Database server. In SQL Server and Azure SQL Database managed instance, this option is set in TempDB and the setting of the individual user databases has no effect.

- Lukasz Szozda