如何预览破坏性的SQL查询?

When writing destructive queries (e.g., DELETE or UPDATE) in SQL Server Management Studio I always find myself wishing I could preview the results of the query without actually running it. Access very conveniently allows you to do this, but I prefer to code my SQL by hand which, sadly, Access is very poor at.

在SQL Server Management Studio中编写破坏性查询(例如，删除或更新)时，我总是希望能够在不实际运行查询的情况下预览查询结果。Access非常方便地允许您这样做，但我更喜欢手工编写SQL，不幸的是，Access非常不擅长。

So my question is twofold:

所以我的问题是双重的:

Is there an add-on for SSMS, or a separate tool that is furnished with good SQL hand-coding facilities that can also preview the result of a destructive query, similar to Access?

SSMS是否有一个附加组件，或者有一个单独的工具，它提供了良好的SQL手工编码功能，可以预览破坏性查询的结果(类似于Access) ?
Are there any techniques or best practices for doing previews "by hand"; e.g., somehow using transactions?

是否有“手工”预览的技术或最佳实践;例如,使用事务吗?

It seems to me that doing this sort of thing is fundamentally important but yet I can't seem to find anything via Google (I'm probably just looking for the wrong thing - I am terribly ignorant on this matter). At present I'm taking a rather hairy belt and braces approach of commenting in and out select/delete/update lines and making sure I do backups. There's got to be a better way, surely?

在我看来，做这种事从根本上来说是很重要的，但我似乎无法通过谷歌找到任何东西(我可能只是在寻找错误的东西——我对这件事一无所知)。目前，我正在使用一个相当多毛的带和大括号的方法来注释和输出选择/删除/更新行，并确保我做了备份。肯定有更好的办法吧?

Can anyone help?

谁能帮忙吗?

5 个解决方案

#1

I would use the OUTPUT clause present in SQL SERVER 2008 onwards...

我将使用SQL SERVER 2008开头的OUTPUT子句……

OUTPUT Clause (Transact-SQL)

输出条款(transact - sql)

Something like...

之类的……

BEGIN TRANSACTION

DELETE [table] OUTPUT deleted.* WHERE [woof] 

ROLLBACK TRANSACTION

INSERTs and UPDATEs can use the 'inserted' table too. The MSDN article covers it all.

插入和更新也可以使用“插入”表。MSDN的文章涵盖了一切。

EDIT:

编辑:

This is just like other suggestions of SELECT then DELETE inside a transaction, except that it actually does both together. So you open a transaction, delete/insert/update with an OUTPUT clause, and the changes are made while ALSO outputting what was done. Then you can choose to rollback or commit.

这就像事务中SELECT then DELETE的其他建议一样，只是它实际上同时执行这两个操作。因此，您可以使用OUTPUT子句打开事务、删除/插入/更新，并在输出所做的内容的同时进行更改。然后您可以选择回滚或提交。

#2

I live in fear of someone doing this to my databases so I always ask my Team to do something like the following:

我生活在对我的数据库做这样的事情的恐惧中，所以我总是要求我的团队做如下的事情:

BEGIN TRAN
 
DELETE FROM X
-- SELECT * FROM X
FROM Table A as X JOIN Table B ON Blah blah blah
WHERE blah blah blah
 
ROLLBACK TRAN
COMMIT TRAN

In this way, if you accidentally hit F5 (done it!) you'll do no changes. You can highlight the SELECT part through the end of the SQL statement to see what records will be changed (and how many). Then, highlight the BEGIN TRAN and entire Delete statement and run it. If you delete the same number of records you expected, highlight the COMMIT TRAN and run it. If anything looks wonky, highlight the ROLLBACK TRAN and run it.

这样，如果你不小心击中了F5(完成了!)，你将不会做任何改变。您可以通过SQL语句的末尾突出显示SELECT部分，以查看将更改哪些记录(以及更改多少)。然后，突出显示BEGIN TRAN和整个Delete语句并运行它。如果您删除了您所期望的相同数量的记录，则突出显示COMMIT TRAN并运行它。如果出现任何问题，请突出显示回滚TRAN并运行它。

I do this with any UPDATE or DELETE statement. It's saved me a couple times, but it ALWAYS provides peace of mind.

我对任何更新或删除语句都这样做。它救过我几次，但它总能让我心平气和。

#3

When deleting:

当删除:

BEGIN TRANSACTION

   DELETE FROM table1 
   OUTPUT deleted.*
   WHERE property1 = 99 

ROLLBACK TRANSACTION

When updating/inserting:

当更新/插入:

BEGIN TRANSACTION

   UPDATE table1
   SET table1.property1 = 99
   OUTPUT inserted.*

ROLLBACK TRANSACTION

#4

When you are in the context of the transaction, you can rollback changes any time before the transaction is committed. (Either by calling commit tran explicitly or if a condition arises that will cause the server to implicitly commit the transaction)

当您在事务上下文中时，您可以在提交事务之前的任何时间回滚更改。(或者显式调用commit tran，或者出现导致服务器隐式提交事务的条件)

create table x (id int, val varchar(10))

insert into x values (1,'xxx')
begin tran

delete from x
select * from x

rollback tran
select * from x

#5

When I want to see what will be deleted, I just change the "delete" statement to a "select *". I like this better than using a transaction because I don't have to worry about locking.

当我想查看要删除的内容时，我只需将“delete”语句更改为“select *”。与使用事务相比，我更喜欢这种方式，因为我不必担心锁的问题。

#1