如何预览破坏性 SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/465083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I preview a destructive SQL query?
提问by Charles Roper
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 中编写破坏性查询（例如，DELETE 或 UPDATE）时，我总是希望能在不实际运行查询的情况下预览查询结果。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?
Are there any techniques or best practices for doing previews "by hand"; e.g., somehow using transactions?
是否有 SSMS 的附加组件，或配备了良好的 SQL 手工编码工具的单独工具，这些工具还可以预览破坏性查询的结果，类似于 Access？
是否有“手动”进行预览的任何技术或最佳实践；例如，以某种方式使用事务？
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?
任何人都可以帮忙吗？
回答by MatBailie
I would use the OUTPUT clause present in SQL SERVER 2008 onwards...
我会使用 SQL SERVER 2008 以后的 OUTPUT 子句...
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.
INSERT 和 UPDATE 也可以使用“插入”表。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 然后 DELETE 的其他建议一样，只是它实际上是同时进行的。因此，您打开一个事务，使用 OUTPUT 子句删除/插入/更新，并在输出所做的更改的同时进行更改。然后你可以选择回滚或提交。
回答by Kevin Buchan
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 并运行它。如果有任何东西看起来不稳定，请突出显示 ROLLBACK TRAN 并运行它。
I do this with any UPDATE or DELETE statement. It's saved me a couple times, but it ALWAYS provides peace of mind.
我使用任何 UPDATE 或 DELETE 语句执行此操作。它救了我几次，但它总是让我安心。
回答by kravits88
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
回答by cmsjr
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
回答by BankZ
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.
当我想查看将要删除的内容时，我只需将“删除”语句更改为“选择 *”。我比使用事务更喜欢这个，因为我不必担心锁定。
