SQL Server query dry run

Question

I run a lot of queries that perform INSERT's, insert SELECT's, UPDATE's and ALTER's on tables, and when developing these queries, the intermediate steps that are run to test that various parts of the query work, potentially change the table or the data within the table.

Is it possible to do a dry run of a query and have SQL Management Studio give you what the results would be, without actually modifying the data or the table structure?

At the moment I have to back up the database, and run the query. If it works, good, if it doesn't, I have to restore the database (which can take around a hour) and I'm trying to avoid wasting all this time having to restore databases.

Answer 1

Use an SQL transaction to make your changes then back them out.

Before you execute your script:

BEGIN TRANSACTION; 

After you execute your script and have done your checking:

ROLLBACK TRANSACTION; 

Every change in your script will then be undone.

Note: Make sure you don't have a COMMIT in your script!

Answer 2

Begin the transaction, perform the table operations, and rollback as shown below:

BEGIN TRAN  UPDATE  C SET column1 = 'XXX' FROM table1 C  SELECT * FROM table1 WHERE column1 = 'XXX'  ROLLBACK TRAN 

This will rollback all the operations performed since the last commit since the beginning of this transaction.