How to test your query first before running it sql server

Question

I made a silly mistake at work once on one of our in house test databases. I was updating a record I just added because I made a typo but it resulted in many records being updated because in the where clause I used the foreign key instead of the unique id for the particular record I just added

One of our senior developers told me to do a select to test out what rows it will affect before actually editing it. Besides this, is there a way you can execute your query, see the results but not have it commit to the db until I tell it to do so? Next time I might not be so lucky. It's a good job only senior developers can do live updates!.

Answer 1

First assume you will make a mistake when updating a db so never do it unless you know how to recover, if you don't don't run the code until you do,

The most important idea is it is a dev database expect it to be messed up - so make sure you have a quick way to reload it.

The do a select first is always a good idea to see which rows are affected.

However for a quicker way back to a good state of the database which I would do anyway is

For a simple update etc

Use transactions

Do a begin transaction and then do all the updates etc and then select to check the data

The database will not be affected as far as others can see until you do a last commit which you only do when you are sure all is correct or a rollback to get to the state that was at the beginning

Answer 2

It seems to me that you just need to get into the habit of opening a transaction:

BEGIN TRANSACTION;

UPDATE [TABLENAME]
SET [Col1] = 'something', [Col2] = '..'
OUTPUT DELETED.*, INSERTED.*       -- So you can see what your update did
WHERE ....;

ROLLBACK;

Than you just run again after seeing the results, changing ROLLBACK to COMMIT, and you are done!

If you are using Microsoft SQL Server Management Studio you can go to Tools > Options... > Query Execution > ANSI > SET IMPLICIT_TRANSACTIONS and SSMS will open the transaction automatically for you. Just dont forget to commit when you must and that you may be blocking other connections while you dont commit / rollback close the connection.