BEGIN TRY DROP TABLE的危险性是什么？

Question

在用于交互式数据子集分析的脚本中，将查询结果存储到临时表中以进行进一步分析通常很有用。

我的许多分析脚本都包含这种结构:

CREATE TABLE #Results (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL
);

INSERT INTO #Results (a, b, c)
SELECT a, b, c
FROM ...

SELECT *
FROM #Results;

在SQL Server中，临时表是连接范围的，因此查询结果会在初始查询执行后持续存在。当我要分析的数据子集计算成本高昂时，我使用这种方法，而不是使用表变量，因为该子集会持续存在于不同批次的查询之间。

脚本的设置部分仅运行一次，随后的查询（SELECT * FROM #Results 这里是一个占位符）则根据需要运行多次。

偶尔，我希望刷新临时表中的数据子集，因此我再次运行整个脚本。其中一种方法是通过将脚本复制到Management Studio中的新查询窗口来创建新连接，但我发现这样很难管理。

相反，我的常规解决方法是在创建语句之前加上一个条件删除语句，如下所示：

IF OBJECT_ID(N'tempdb.dbo.#Results', 'U') IS NOT NULL
BEGIN
  DROP TABLE #Results;
END;

这个语句正确地处理了两种情况：

1. 在第一次运行时表不存在：不执行任何操作。
2. 在后续运行中表存在：删除表。

我编写的生产脚本总是使用这种方法，因为它对于这两种预期情况都不会引发错误。

我的同事编写的一些等效脚本有时使用异常处理来处理这两种情况：

BEGIN TRY DROP TABLE #Results END TRY BEGIN CATCH END CATCH

我相信在数据库世界里，总是征得许可比寻求原谅更好，所以这种方法让我感到不安。

第二种方法会吞掉错误，而不采取任何处理非异常行为的措施（表不存在）。此外，可能会因为其他原因而引发错误，而不是表不存在。 Wise Owl也对同样的事情提出了警告：

在这两种方法中，[OBJECT_ID方法] 更难理解，但可能更好：使用[BEGIN TRY方法]，你有可能会陷入错误的陷阱！

但它没有解释实际风险是什么。

在实践中，BEGIN TRY方法从未在我维护的系统中导致问题，所以我很高兴它能留在那里。

使用BEGIN TRY方法管理临时表存在的可能危险是什么？哪些意外错误可能会被空catch块隐藏？

- Iain Samuel McLean Elder

Answer 1

可能有哪些危险？ 可能隐藏了哪些意外错误？

如果try catch块在事务内部，它会导致失败。

BEGIN
BEGIN TRANSACTION t1;
SELECT 1

BEGIN TRY DROP TABLE #Results END TRY BEGIN CATCH END CATCH

COMMIT TRANSACTION t1;
END

这个批处理将会出现如下错误：

Msg 3930, Level 16, State 1, Line 7 当前事务无法提交，也无法支持写入日志文件的操作。回滚事务。 Msg 3998, Level 16, State 1, Line 1 批处理结束时检测到未提交的事务。事务已回滚。

Books Online 记录了这种行为：

未提交的事务和 XACT_STATE

如果在 TRY 块中生成的错误导致当前事务的状态无效，则该事务被归类为未提交的事务。在 TRY 块内发生错误时，通常会结束事务的错误会导致事务进入未提交状态。未提交的事务只能执行读取操作或回滚事务。该事务不能执行任何生成写操作或提交事务的 Transact-SQL 语句。

现在使用测试方法替换 TRY/Catch

BEGIN
BEGIN TRANSACTION t1;
SELECT 1

IF OBJECT_ID(N'tempdb.dbo.#Results', 'U') IS NOT NULL
BEGIN
  DROP TABLE #Results;
END;

COMMIT TRANSACTION t1;
END

并再次运行。事务将在没有任何错误的情况下提交。

- ClearLogic

---

谢谢您回答我提出的问题！也许我在过多地描述我的特定用例，以至于其他人无法看到那些带有问号的陈述。 - Iain Samuel McLean Elder

Books Online记录了错误对事务的影响，因此我添加了一个错误消息的示例和文档摘录。谢谢！ - Iain Samuel McLean Elder

感谢您改进答案，我突出了这些问题，因为在我看来它们非常有效。 - ClearLogic

这个问题是开放式的，但这是目前为止最好的答案，所以我会接受它。 - Iain Samuel McLean Elder

Answer 2

更好的解决方案可能是使用表变量而不是临时表。

例如：

declare @results table( 
  a INT NOT NULL, 
  b INT NOT NULL, 
  c INT NOT NULL 
); 

- podiluska

---

我已经编辑了我的问题，解释了为什么在这种情况下表变量不合适。 - Iain Samuel McLean Elder

Answer 3

我认为try块很危险，因为它可能隐藏意外的问题。一些编程语言只能捕获特定的错误并且不能捕获意外错误，如果你的编程语言具有此功能，则应使用它（T-SQL 无法捕获特定错误）

对于您的情况，我可以解释我像您一样编写代码，使用try catch块。

期望的行为是：

begin try
   drop table #my_temp_table
end try
begin catch __table_dont_exists_error__
end catch

但是这个不存在！那么你可以写一些像这样的东西：

begin try
   drop table #my_temp_table
end try
begin catch 
  declare @err_n int, @err_d varchar(MAX)
  SELECT 
    @err_n = ERROR_NUMBER() ,
    @err_d = ERROR_MESSAGE() ;
  IF @err_n <> 3701 
     raiserror( @err_d, 16, 1    )     
end catch

当删除表的错误不是'table don't exists'时，这将触发一个事件。

请注意，对于您的问题，所有这些代码都不值得。但对于其他方法可能有用。对于您的问题，优雅的解决方案是 仅在存在时删除表 或使用 表变量。

- dani herrera

Answer 4

在你的问题之外，但可能被忽视的是临时表使用的资源。我总是在脚本结束时删除表格，这样它就不会占用资源。如果您将一百万行放入表中怎么办？那么我还会在脚本开始时测试表格，以处理上次运行中出现错误并且表格未被删除的情况。如果您想重复使用临时表，则至少清除行。

表变量是另一个选择。它更轻巧，但也有限制。如果要在查询连接中使用表变量，请避免使用表变量，因为查询优化器无法像处理临时表那样处理表变量。

SQL文档：

如果在单个存储过程或批处理内创建了多个临时表，则它们必须具有不同的名称。

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

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

在存储过程中创建的本地临时表在存储过程完成时会自动删除。该表可以被由创建表的存储过程执行的任何嵌套存储过程引用。该表无法被调用创建表的存储过程引用的进程。

所有其他本地临时表都会在当前会话结束时自动删除。

全局临时表在创建该表的会话结束并且所有其他任务停止引用它们时会自动删除。任务和表之间的关联仅在单个 Transact-SQL 语句的生命周期内维护。这意味着当创建会话结束时，最后一个积极引用表的 Transact-SQL 语句完成后，全局临时表将被删除。

- paparazzo

---

你提到的资源利用方面的观点很有价值，但我正在寻找使用异常处理方法的风险。我已经尝试澄清我的问题。我特别使用临时表，因为它们在查询批次之间保持持久性。无论如何，感谢您的回答。如果您引用文档，请提供参考资料。 - Iain Samuel McLean Elder

我知道你的问题是测试（test）与尝试（try），但我的问题是为什么要这样刷新呢？如果您需要清除行，则截断（truncate）比删除和重新创建要高效得多。 - paparazzo

我从未考虑过截断而不是删除和重新创建。这会使条件逻辑更加复杂。设置的效率并不像查询的效率那样重要。但这可能有效。 - Iain Samuel McLean Elder