我应该提交或回滚创建临时表、读取然后删除它的事务吗？
要选择与数百个 ID 的列表相关的信息...而不是创建一个巨大的 select 语句，我创建临时表，将 id 插入其中，将其与表连接以选择与 ID 匹配的行，然后删除临时表桌子。因此，这本质上是一个读取操作，不会对任何持久数据库表进行永久更改。
我在事务中执行此操作，以确保完成后删除临时表。我的问题是......当我提交这样的事务与让它回滚时会发生什么？
性能方面...数据库引擎是否需要做更多的工作来回滚事务而不是提交事务？由于唯一的修改是对临时表进行的，是否存在差异？
相关问题在这里，但没有回答我涉及临时表的具体情况： 我应该提交还是回滚读取事务吗？
编辑（问题澄清）：
在提交/回滚之前不寻求建议。交易是绝对必要的。假设没有发生错误。假设我已经创建了一个临时表，假设我知道对 tempdb 的真正“工作”写入已经发生，假设我在事务中执行只读（选择）操作，并假设我在临时表上发出删除语句。毕竟……提交和回滚哪个更便宜，为什么？根据涉及临时表和其他只读操作的特定场景，数据库引擎在提交与回滚时还可以做哪些其他工作？
To select information related to a list of hundreds of IDs... rather than make a huge select statement, I create temp table, insert the ids into it, join it with a table to select the rows matching the IDs, then delete the temp table. So this is essentially a read operation, with no permanent changes made to any persistent database tables.
I do this in a transaction, to ensure the temp table is deleted when I'm finished. My question is... what happens when I commit such a transaction vs. let it roll it back?
Performance-wise... does the DB engine have to do more work to roll back the transaction vs committing it? Is there even a difference since the only modifications are done to a temp table?
Related question here, but doesn't answer my specific case involving temp tables: Should I commit or rollback a read transaction?
EDIT (Clarification of Question):
Not looking for advice up to point of commit/rollback. Transaction is absolutely necessary. Assume no errors occur. Assume I have created a temp table, assume I know real "work" writing to tempdb has occurred, assume I perform read-only (select) operations in the transaction, and assume I issue a delete statement on the temp table. After all that... which is cheaper, commit or rollback, and why? What OTHER work might the db engine do at THAT POINT for a commit vs a rollback, based on this specific scenario involving temp-tables and otherwise read-only operations?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果我们谈论的是本地临时表（即名称以单个
#为前缀），那么当您关闭连接时，SQL Server将杀死该表。因此，假设您的数据层经过精心设计，可以在尽可能短的时间内保持连接打开，我不会担心在事务中包装临时表的创建。我认为将表包装在事务中可能会有轻微的性能差异，但我敢打赌，与由于创建和填充临时表的时间而使事务保持更长时间打开的成本相比，它是如此之小，以至于微不足道。
If we are talking about local temporary table (i.e. the name is prefixed with a single
#), the moment you close your connection, SQL Server will kill the table. Thus, assuming your data layer is well designed to keep connections open as short a time as possible, I would not worry about wrapping the creation of temp tables in a transaction.I suppose there could be a slight performance difference of wrapping the table in a transaction but I would bet it is so small as to be inconsequential compared to the cost of keeping a transaction open longer due to the time to create and populate the temp table.
确保删除临时表的一种更简单的方法是使用 # 符号创建它。
创建表#mytable (
行 ID 整数，
rowName char(30) )
# 告诉 SQL Server 该表是本地临时表。该表仅对该 SQL Server 的该会话可见。当会话关闭时，该表将自动删除。您可以像对待任何其他表一样对待该表，但有一些例外。唯一真正的主要问题是临时表上不能有外键约束。其他内容包含在在线书籍中。
临时表是在 tempdb 中创建的。
如果您这样做，则不必将其包装在事务中。
A simpler way to insure that the temp table is deleted is to create it using the # sign.
CREATE TABLE #mytable (
rowID int,
rowName char(30) )
The # tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When the session is closed, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table. The others are covered in Books Online.
Temporary tables are created in tempdb.
If you do this, you won't have to wrap it in a transaction.