存储过程中的临时表

Question

我一直在思考存储过程中的临时表以及它们如何影响并发性。 该存储过程是在MSSQL 08服务器上创建的。

如果我有一个存储过程，在其中创建一个临时表，然后像这样将其删除：

BEGIN

CREATE TABLE #MyTempTable
(
   someField int,
   someFieldMore nvarchar(50)
)

... Use of temp table here
... And then..

DROP TABLE #MyTempTable

END

这个存储过程将会被非常频繁地调用，所以我的问题是这里是否可能出现并发问题？

- The real napster

Answer 1

不是。每个连接将会创建独立的临时表实例。

- Mehrdad Afshari

---

好的，这也是我想的...我有点紧张，因为我在使用查询分析器时建立了一个临时表，并且在稍后能够再次调用它，而没有将其删除。但是根据你的帖子，我尝试打开一个新的查询，并尝试从那里调用它，但没有成功，现在我又冷静下来了 :) - The real napster

@tpower：我对OP的理解主要是关于修改共享状态和线程问题。很明显，性能会受到影响。 - Mehrdad Afshari

我强烈建议按照@blowdart的回答使用表变量。表变量的作用域仅限于当前存储过程（因此无需删除它）。我有一个嵌套的存储过程，使用了同名的临时表，因此导致错误，因为临时表被嵌套的存储过程删除，因此在父存储过程中不再可用。 - tkerwood

Answer 2

也许可以。

#开头的临时表（如#example）在每个会话中都被保留。因此，如果您的代码在另一个调用正在运行时（例如后台线程）再次调用存储过程，则创建调用将失败，因为它已经存在。

如果你真的很担心，就使用一个表变量吧。

DECLARE @MyTempTable TABLE 
(
   someField int,
   someFieldMore nvarchar(50)
)

这将特定于该存储过程调用的"实例"。

- blowdart

---

无论客户端尝试做什么，您都无法重用SQL会话。 - gbn

会话池 - 它将在一定时间内回收会话，因此如果您忘记删除临时表，它可能仍然存在。 - blowdart

但只要始终调用“drop table”，这就不是一个问题，对吧？ - The real napster

重新提出一个旧问题：如果我使用 declare @temp table ... 创建一个表，退出前是否仍需要调用 drop @temp？ - user153923

你可能已经意识到了，但我再明确一下，给以后读这篇文章的人看。表变量会自动删除，你不需要显式地进行操作。 - Polynomial Proton

Answer 3

并不是，我在谈论 SQL Server。单个 # 的临时表存在并且在创建的作用域内可见（作用域绑定）。每次调用存储过程都会创建一个新的作用域，因此该临时表仅存在于该作用域中。我认为，这些临时表也对在该作用域内调用的存储过程和 UDF 可见。但如果您使用双重井号（##），则它们将成为会话中全局的，并因此对其他执行进程可见。您需要考虑临时表被同时访问的可能性是否可取。

- Mehmet Aras

Answer 4

根据SQL Server 2008 Books的说明，您可以创建本地和全局临时表。本地临时表只在当前会话中可见，而全局临时表对所有会话都可见。

'#table_temporal 是本地临时表。

'##table_global 是全局临时表。

如果在能够同时被多个用户执行的存储过程或应用程序中创建本地临时表，则数据库引擎必须能够区分不同用户创建的表。数据库引擎通过在每个本地临时表名称后面内部添加数字后缀来实现这一点。

然后就不会出现问题了。

- fperez

---

关于全局临时表，需要注意的是，只有在同一 spid 引用或新的 spid 可以保证旧的 spid 仍然存在时，它们才是安全的。当创建 spid 终止时，即使被另一个 SPID 引用，全局临时表也会被删除。 - Justin

Answer 5

对于那些建议使用表变量的人，请谨慎使用。表变量不能被索引，而临时表可以。当处理少量数据时，最好使用表变量，但如果您处理的是大量数据（例如50k条记录），则临时表比表变量快得多。

此外，请记住，您不能依赖try/catch来强制存储过程中的清理。某些类型的失败无法在try/catch中捕获（例如由于延迟名称解析导致的编译失败）。如果您想确保，您可能需要创建一个包装存储过程，该过程可以对工作存储过程进行try/catch并在其中执行清理。

例如： create proc worker AS BEGIN -- 在此处执行某些操作 END

create proc wrapper AS
BEGIN
    Create table #...
    BEGIN TRY
       exec worker
       exec worker2 -- using same temp table
       -- etc
    END TRY
    END CATCH
       -- handle transaction cleanup here
       drop table #...
    END CATCH 
END

表变量总是有用的一种地方在于它们不会在事务回滚时被回滚。这对于捕获您想要提交到主要事务之外的调试数据非常有用。

- Justin

Answer 6

简短回答：不

---

解释:

根据官方文档：https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

如果在可以被多个用户同时执行的存储过程或应用程序中创建了一个本地临时表，数据库引擎必须能够区分由不同用户创建的表。数据库引擎通过在每个本地临时表名后面内部附加一个数字后缀来实现这一点。在 tempdb 中存储的临时表的完整名称由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许使用后缀，本地临时名称指定的 table_name 不能超过116个字符。

- Duc Filan

Answer 7

数据库对所有 #temp 表使用相同的锁，因此如果您使用了很多，就会出现死锁问题。最好使用 @table 变量来实现并发。

- tpower

Answer 8

尽可能使用@temp表——也就是说，您只需要一个主键，并且不需要从下级存储过程访问数据。

如果您需要从下级存储过程访问数据（它是存储过程调用链的邪恶全局变量），并且没有其他干净的方法在存储过程之间传递数据，请使用#temp表。如果您需要第二个索引，请使用它（尽管，如果您需要多个索引，真的要问问自己是否需要#temp表）

如果这样做，请始终在函数顶部声明您的#temp表。当SQL看到create table语句时，将强制重新编译您的存储过程...因此，如果您在存储过程中间声明#temp表，则必须停止处理并重新编译存储过程。

- muststayanonymous

---

请看我的注释，如果临时表中的数据量过大，@temp表可能会变得危险，并且可能会显著影响存储过程/查询的性能。 - Justin