Some Pain Is Avoidable - So Spare Yourself

Ever heard of a runaway F5? This is when you intend to highlight only a small section of SQL code but you miss for some reason and when you hit F5, the entire script you have loaded runs instead of just the small part you tried to highlight.

It's not enough to put a syntax error at the top of your script because there might be some batch separating GOs in there. So there are a few things that I routinely do to protect against this because the results of such a mistake can be, shall we say, painful.

1. Put this at the top:

create database trash
GO
use trash
GO

This will make most of your code run in the wrong context. The GO statements are important as you need these to run without errors.

If your code uses three-part names to point to specific databases, this will not help, so keep reading.

2. Don't use the database name in dev-only code, and think carefully about your use of database context in your production code. If you put a USE statement in your manually-attended script, hide it inside comments:

/*
USE Somedatabase
*/

-- USE Somedatabase

The block comment way makes highlighting the whole line easier if and when you need to run it.

3. Comment out the modification part of your statements and turn them into selects:

Instead of the following query:

UPDATE MyTable
SET Salesrep = 42
WHERE CustID = 16

This is a much safer query to leave lying around in your script:

SELECT
   SalesRep,
   NewSalesRep = 42,
   *
-- UPDATE M SET Salesrep = 42
FROM MyTable M
WHERE CustID = 16

The highlighted part is what you have to select in order to run your update (note that you're skipping the commenting characters at the beginning of the line). You can still make a mistake by leaving off the WHERE clause, but at least you know no updates will be made unless you do some careful selecting.

Here's one rough idea for a way to protect against forgetting the where clause:

SELECT
   SalesRep,
   NewSalesRep = 42,
   *
-- BEGIN TRAN UPDATE M SET Salesrep = 42
FROM MyTable M
WHERE CustID = 16
ROLLBACK TRAN
-- COMMIT TRAN

You can still blow it but it's harder. Any full-line end selection is protected.

And another, simpler and possibly better:

SELECT
   SalesRep,
   NewSalesRep = 42,
   *,
-- UPDATE M SET Salesrep = 42
FROM MyTable M /*
*/ WHERE CustID = 16

A full-line end selection is again protected.

4. Be paranoid. Don't let yourself get comfortable. THINK about each update you do to real, live, data. Make an extra "unnecessary" backup. Convert your modification statements to selects and check it. Select the results into a new table and do some joins to make sure they were right. Run the update in a transaction and do some queries to verify them. Keep in mind potential locking & blocking, though, so if people are accessing the table you're modifying, create the check statements and do the update and the check all at once:

BEGIN TRAN

UPDATE Blah -- My potentially dangerous modification

SELECT * FROM Blah B INNER JOIN Gorp G ON Splat = Bonk

ROLLBACK TRAN

Only run the whole block. If you don't like what you got, fix it, and run it again.

Be wary of leaving open any transaction, even one you don't think will affect other people. A stray SQL editor session holding some kind of lock is annoying and can cause problems.

I hope these ideas are useful to you and save you some pain! I had to learn this the hard way. I am simply fortunate that I never did anything REALLY bad before acquiring these habits.

Update 2008-05-16

5. If you must have a USE statement, check the server:

IF @@Server = 'DevServerName' USE DevDatabase

Depending on what you're doing in your code and how potentially destructive it is, you can do some pretty clever things to help prevent problems. Use the tools at your disposal.