How do I "test" a sql query that modifies data, i.e. see the output before committing in sql server 2008?

Question

I have used other SQL tools and some allow me to see the output of a threatening query before committing. I like this two step process (so I can double check I'm not doing something bad).

Is there a way in SQL Server 2008 R2 to "execute" a query, see the output for the affected rows, and then choose to accept or throw away the commit?

EDIT:

I also found another question asking the same with a different answer using the OUTPUT clause.

How do I preview a destructive SQL query?

Answer 1

Yes this is possible. You can either use the session option SET IMPLICIT_TRANSACTIONS ON or create an explicit transaction as below.

BEGIN TRAN

UPDATE YourTable 
SET foo=1
 /*To Review Changes can use OUTPUT clause here...*/
OUTPUT INSERTED.*, DELETED.*
WHERE bar=2


/*... or a SELECT against the table*/
SELECT *
FROM YourTable
WHERE bar=2


-- Execute the COMMIT or ROLLBACK commands when ready

However you should be aware that your open transaction will hold locks until the transaction completes which may block other transactions so this should be used with caution in any multi user environment.

Answer 2

BEGIN TRAN TEST
UPDATE TABLE_NAME
SET Col='TEST'

After viewing the results you can either COMMIT or ROLLBACK the transaction.

USE SELECT @@TRANCOUNT to see the number of open transactions that you have in your current connection.