存储过程中的临时表：是否删除？

Question

我已经遇到这个问题很多次了，但一直无法找到一个完全满足我的答案。通常，大多数人和书籍都会说：“虽然临时表在超出范围时被删除，但当它们不再需要时，应该显式地删除它们，以减少服务器上的资源需求”。

对我来说很清楚，在Management Studio中创建表时，您将使用该表的某些资源，直到关闭窗口或断开连接为止。因此，当表不再需要时删除表是合乎逻辑的。

但是，当您使用存储过程时，如果您想要清理表格，则最有可能在过程结束时执行（我不是在谈论您在过程中不再需要即删除表的情况）。因此，工作流程如下：

当您在存储过程中删除时：

• 开始执行存储过程
• 完成一些工作
• 删除表
• 执行结束

据我所知，如果不删除，它怎么可能正常工作：

• 开始执行存储过程
• 完成一些工作
• 执行结束
• 删除表

这里有什么区别？ 我只能想象需要某些资源来识别临时表。 您有其他想法吗？

更新：

我进行了一个简单的测试，使用了两个存储过程：

create procedure test  as
begin
create table #temp (a int)
insert into #temp values (1);
drop table #temp;
end

还有一个没有drop语句的。我启用了用户统计信息并运行了测试：

declare @i int = 0;
 while @i < 10000
 begin
 exec test;
 SET @i= @i + 1;
 end

以下是结果（试验1-3删除存储过程中的表，试验4-6不删除它）：

正如图片所示，如果我不删除临时表，则所有统计数据都相同或略有下降。

更新2：

我进行了第二次测试，调用次数为100,000次，并添加了SET NOCOUNT ON。这些是结果:

正如第二次运行所证实的那样，如果您不在存储过程中删除表，则实际上可以节省一些用户时间，因为这是由用户时间之外的某个其他内部进程完成的。

- Dmitrij Kultasev

---

你考虑过表变量吗？http://odetocode.com/articles/365.aspx 使用它们，您无需担心删除。 - UnDiUdin

您不需要费心去删除临时表。在正常情况下，两者都被取消作用域（尽管临时表是在语句级别上，而表变量则与当前SPID相关）。但表变量也有一整套的优缺点列表。 - Xedni

似乎我有点错了。我正在阅读 SQL Server 2016 的官方培训材料，上面写着：“虽然临时表在超出其范围后会被删除，但应在不再需要它们时显式地删除它们，以减少服务器的资源需求。” - Dmitrij Kultasev

我发现在需要将代码从存储过程中复制粘贴出来单独运行时，包括一个DROP语句可以很有用 - 但当在存储过程的上下文中运行时，它将自动清理（删除或缓存）。 - Martin Smith

你的测试结果很有意义 - 显式的 DROP 操作必须等待 drop/cache 操作完成 (尽管对于大表格可以进行延迟删除)，而在隐式情况下，清理操作可以在过程执行完毕后进行... - Martin Smith

Answer 1

你可以在 Paul White 的文章中阅读更多关于这方面的信息：存储过程中的临时表

CREATE 和 DROP 不需要执行

我会在我的下一篇文章中详细讨论这个问题，但关键点是，如果可以缓存临时对象，则 CREATE TABLE 和 DROP TABLE 在存储过程中不会创建和删除临时表。 当执行 DROP TABLE 时，临时对象被重命名为内部形式，并在下次执行时遇到 CREATE TABLE 时重命名回同样的用户可见名称。此外，自动创建在临时表上的任何统计信息也会被缓存。这意味着在下次调用存储过程时，之前执行的统计信息仍然存在。

- sepupic

---

虽然这是我学到的新知识，但仍然没有回答删除存储过程末尾是否有任何性能/空间等方面的好处。 - Dmitrij Kultasev

是的，它已经回答了。这意味着，如果您在存储过程中删除或不删除临时表，实际上并没有任何区别，因为无论如何它都将被缓存而且不会被删除。 - sepupic

但在这种情况下，存在差异，因为您不会在存储过程中执行实际语句，很可能它是在其外部以某种方式执行的。很可能这就是为什么在我的测试中，没有删除统计信息的结果略微更好的原因。 - Dmitrij Kultasev

抱歉，我不明白你所说的区别。无论如何，如果你是要删除还是不删除，表格都将被重命名和缓存，这将在你发出显式删除或退出你的sp时发生。 - sepupic

我的意思是实现了一些逻辑，然后您或外部进程执行“DROP”语句。我同意它并没有真正删除表格，但仍然对该表格进行了某些处理。因此，当您将删除语句包含在SP中时，它是使用用户时间完成的，但如果您不这样做，则由另一个进程完成，这不会影响用户时间。我希望我的观点现在很清楚。（请参见OP中的UPDATE 2部分） - Dmitrij Kultasev

Answer 2

从技术上讲，一个本地范围的临时表（前面带有一个井号）将在SPID关闭后自动退出作用域。有一些非常奇怪的情况，你会得到一个缓存了临时表定义但没有真正删除的情况。通常发生在嵌套包含同名临时表的存储过程调用中。

当你完成使用表后及时删除它们是个好习惯，但除非出现意外情况，它们应该在存储过程结束时自动退出作用域。

- Xedni

---

我更关心性能问题，而不是嵌套的并发问题。 - Dmitrij Kultasev

明白了。Drop语句虽然简单，但并非没有开销。我猜想如果你像现在这样频繁地调用该过程，那么删除操作可能会给该过程增加一定的时间负担（将!10批次总数除以10000次执行，意味着你大约每0.6秒就要删除该表）。如果你真的很好奇，我建议运行分析器跟踪，看看在反复调用该过程时，不同部分被分配了多少时间。但这也提出了一个很好的观点。在高容量的过程中，删除（甚至创建）临时表可能不是最划算的选择。 - Xedni