How do I preview a destructive SQL query?

Question

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.

So my question is twofold:

1. 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?

2. 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?

Answer 1

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

OUTPUT Clause (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.

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.

Answer 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.

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

Answer 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

Answer 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)

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

Answer 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.