CODEWITHSUNDEEP
sqlsql-server
Ageis
Ageis

Reputation: 2315

How to test your query first before running it sql server

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

Upvotes: 14

Views: 27061

Answers (5)

GeekInDisguise
GeekInDisguise

Reputation: 1567

Have you considered explain ?

If there is a mistake in the command, it will report it as with usual commands.

But if there are no mistakes it will not run the command, it will just explain it.

Example of a "passed" test:

testdb=# explain select * from sometable ;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on sometable  (cost=0.00..12.60 rows=260 width=278)
(1 row)

Example of a "failed" test:

testdb=# explain select * from sometaaable ;
ERROR:  relation "sometaaable" does not exist
LINE 1: explain select * from sometaaable ;

It also works with insert, update and delete (i.e. the "dangerous" ones)

Upvotes: 0

Pedro Lorentz
Pedro Lorentz

Reputation: 2326

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.

Upvotes: 22

BlakeH
BlakeH

Reputation: 3494

For our production data changes, we always ensure that we use a BEGIN TRAN and a ROLLBACK TRAN and then all statements have an OUTPUT clause. This way we can run the script first (usually in a copy of PRODUCTION db first) and see what is affected before changing the ROLLBACK TRAN to COMMIT TRAN

Upvotes: 0

mmmmmm
mmmmmm

Reputation: 32720

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

Upvotes: 8

octopusgrabbus
octopusgrabbus

Reputation: 10695

If you must test in a production database and you have the requisite permissions, then write your queries to create and use temporary tables that in name are similar to the production tables and whose schema other than index names is identical. Index names are unique across a databse, at least on Informix.

Then run your queries and look at the data.

Other than that, IMHO you need a development database, and perhaps even a development server with a development instance. That's paranoid advice, but you'd have to be very careful, even if you were allowed -- MS SQLSERVER lingo here -- a second instance on the same server.

I can reload our test database at will, and that's why we have a test system. Our production system contains citizens' tax payments and other information that cannot be harmed, "or else".

Upvotes: 0

Related Questions