为什么 DROP TABLE 在 SELECT INTO 之前似乎没有生效?

本文介绍了为什么 DROP TABLE 在 SELECT INTO 之前似乎没有生效?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

以下 tSQL 查询令我困惑:

The following tSQL query is puzzling me:

select 1 as FIELD into #TEMP drop table #TEMP select 1 as FIELD into #TEMP

当我从 SQL Server Management Studio 会话窗口运行它时(按 F5 到整个查询，作为一个组)，我收到以下错误:

When I run it from SQL Server Management Studio session window (pressing F5 to the whole query, as a group), I get the following error:

消息 2714，级别 16，状态 1，第 3 行数据库中已经有一个名为#TEMP"的对象.

Msg 2714, Level 16, State 1, Line 3 There is already an object named '#TEMP' in the database.

请注意，在执行查询之前，表 #TEMP 不存在.

Note that table #TEMP doesn't exist before the query is executed.

我认为代码不应该产生任何错误，因为第 2 行正在删除临时表.但是就好像执行第3行时drop没有生效.

I thought that the code shouldn't produce any errors as line 2 is dropping the temporary table. But it is as if the drop isn't taking effect when line 3 is executed.

我的问题:

为什么会发生错误?

如何修复查询以使其按预期执行?

附注.上面的查询是对我的真实世界查询的简化，它显示了相同的症状.

PS. The query above is a simplification of a real world query of mine that is showing the same symptoms.

PS2.不管这是否是一种合理的编程实践(正如 Sean 在他的评论中暗示的那样)，这种意外行为促使我寻找有关如何解析这些查询的信息，希望这些知识对我将来有所帮助.

PS2. Regardless of whether this is a sound programming practice or not (as Sean hinted in his comments), this unexpected behavior prompted me to look for information on how these queries are parsed in the hopes that the knowledge will be helpful to me in the future.

推荐答案

我发现现有表的查找方式不同:

As I found the seek of existing tables are different:

select 1 as FIELD into #TEMP drop table #TEMP

当你在这些命令之后使用 into 语句时:

When you use into statement after those commands:

select 1 as FIELD into #TEMP

错误是:

数据库中已经有一个名为#TEMP"的对象.

There is already an object named '#TEMP' in the database.

并且当您在这些命令之后在 #TEMP 上使用 select 时:

And When you use a select on #TEMP after those commands:

select * from #TEMP

错误是:

无效的对象名称#TEMP".

Invalid object name '#TEMP'.

所以，在第一种情况下，有一个名为 #TEMP 的对象，在另一种情况下，没有一个名为 #TEMP 的对象！

So, In first case THERE IS an object with #TEMP name and in the other case THERE IS NOT an object with #TEMP name !.

technet.microsoft 的一个重要说明是:

DROP TABLE 和 CREATE TABLE 不应在同一个批处理中的同一个表上执行.否则可能会出现意外错误.

DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

在 SQL Server 数据库引擎删除表的注释中:

In notes of dropping tables by SQL Server Database Engine:

SQL Server 数据库引擎将实际的页面释放及其关联的锁推迟到事务提交之后.

The SQL Server Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits.

因此，使用 select 语句的第二个错误可能与实际页面释放有关，而使用 into 语句的第一个错误可能与在事务提交之前关联的锁之间的持续时间.

So the second error on using select statement may related to the actual page deallocations and the first error on using into statement may related to duration between lock associated until the transaction commits.