SQLServer 中的本地和全局临时表

SQLServer 中本地临时表和全局临时表的区别是什么？

322187 次浏览
小开
最佳答案

我发现这个解释非常清楚(它纯粹是从 Technet拷贝过来的) :

临时表有两种类型: 本地表和全局表。与首次创建或引用本地临时表时相同，在与 SQLServer 实例的连接期间，本地临时表只对其创建者可见。用户从 SQLServer 实例断开连接后，将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的，当所有引用该表的用户与 SQLServer 实例断开连接时，将删除该表。

小开

引自在线图书:

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

临时表在超出作用域时会自动删除，除非使用 DROPTABLE 显式删除:

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

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

  • 局部临时表 (CREATE TABLE #t)只对 创建它的连接，并在连接为 关门了

  • 每个人都可以看到全局临时表 (CREATE TABLE ##t) , 并且当所有引用它们的连接都关闭时将被删除。

  • 可以看到 Tempdb 永久表 (USE tempdb CREATE TABLE t) 当服务器重新启动时，将删除它们。

小开

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

本地临时表只对 SQLServer 会话或 创建表的连接(意味着单个用户) 当创建表的会话具有 本地临时表名以单个散列表示 (“ #”)签署。

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

局部临时表的作用域存在于当前 用户意味着当前查询窗口。如果您将关闭当前 查询窗口或打开一个新的查询窗口，并将尝试查找以上内容 创建的临时表，它会给你的错误。

2)一个全球性的临时组织表永久保留在数据库中，但是行只存在于给定的连接中。当连接关闭时，全局临时表中的数据将消失。但是，当下次打开数据库时，表定义将保留在数据库中以供访问。

全局临时表可用于所有 SQLServer 会话或 连接(意味着所有用户)。这些可以由任何 SQL 创建 服务器连接用户和这些被自动删除时，所有 已关闭 SQLServer 连接。全局临时表 名称使用双散列(“ # #”)符号。

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

所有 SQLServer 连接都可以看到全局临时表 而本地临时表只对当前 SQLServer 可见 联系。

小开

值得一提的是，还有: 数据库作用域全局临时表(目前只有 Azure SQL 数据库支持)。

SQLServer 的全局临时表(以 # # 表名发起)存储在 temdb 和 在整个 SQLServer 实例的所有用户会话之间共享。中

Azure SQL 数据库支持全局临时表，这些表也存储在 temdb 中，作用域限定在数据库级别。这意味着全局临时表在同一个 Azure SQL 数据库中为所有用户的会话共享。来自其他数据库的用户会话无法访问全局临时表。

-- 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'

更改数据库范围配置

GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }

APPIES TO: Azure SQL 数据库(特性在公开预览中)

允许为全局临时设置自动删除功能 默认值为 ON，这意味着全局临时 表在不被任何会话使用时自动删除。 < strong > When 设置为 OFF，则需要显式删除全局临时表 使用 DROPTABLE 语句或将自动删除 服务器重新启动

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

小开

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

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

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

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

小开

我没有看到任何答案显示用户在哪里可以找到一个全球临时表。在 SSMS 中导航时，可以在同一位置查看 Local 和 Global 临时表。下面的截图来自 这个链接。

数据库—— > 系统数据库—— > temdb —— > 临时表

enter image description here


提交答案