Spare Yourself Some Pain 2

[ESquared]

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, pointing to specific databases in your code, this will not help, so there are more things possible.

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 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 more safe query to leave lying around in your script:

SELECT
   SalesRep,
   NewSalesRep = 42,
-- [COLOR=white #800080]UPDATE M SET Salesrep = 42
FROM MyTable M
WHERE CustID = 16[/color]

The highlighted part is what you have to select in order to run your update. 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:

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.

 

[ESquared]

Sigh.. forgot the * in there:

SELECT
   SalesRep,
   NewSalesRep = 42,
   [COLOR=black yellow][b]*[/b][/color]
-- UPDATE M SET Salesrep = 42
FROM MyTable M
WHERE CustID = 16

The point is to see the rows that will be updated and do a sanity check: is the rowcount right? Am I seeing the right data? Do the before & after values make sense? And so on.

 

[kaht]

I've gotten into the habit of adding a comment -- to every insert, delete, and update in my query analyzer after it has been run for this very reason. I've accidentally hit f5 w/o selected text a few times but it was always safe because of the comments.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

Finally, <. is a good thing!

http://www.lessthandot.com

 

[philhege]

Yay, comments.

I sometimes put a check for the instance name at the top:

IF @@servername <> 'TheSafeDevServer'
BEGIN
raiserror 'No, no, no; not here, doofus.'
RETURN
END

Of course if your unintentional updates would cause pain in the dev environment, the comment is still your friend.

Phil H.
Some Bank
-----------
I used to be happy programming. Now I'm miserable project planning. Hey, at least the money's good.