Protecting Against Delete without Where

If I’m doing any manual work with T-SQL, I always begin every set of data change operations with BEGIN TRAN and I have the COMMIT TRAN commented out at the end of my script. Why?

Quite simply, because I’m wary of having a DELETE clause without a proper WHERE clause. I’ve made that mistake in my career and it’s easy to do if you are tired, pressed for time, or both. This allows me to verify I’ve only changed what I intended and then I highlight and execute the COMMIT TRAN to finish the operation. However, there have been times when I neglect this habit. 

Which is why seeing Steve Jones’ post about a new feature in SQL Prompt made me smile. If you have it installed and are about to execute a DELETE without a WHERE clause, it prompts you to verify this was your intent. As Steve writes, sometimes it is. But when it isn’t, that warning can be a life saver. 

I won’t abandon my BEGIN TRAN habit, but I will certainly take advantage of this new feature in SQL Prompt. 

Advertisements

7 Comments (+add yours?)

  1. Jay Coppola
    Jun 13, 2016 @ 07:34:06

    I always try and form my delete or update queries so I can verify them with a select statement (Commented out).
    Something like…
    Update X
    Set X.Field1 = 42
    –Select *
    From dbo.table1 X
    Join dbo.tableY Y On Y.FK = X.PK
    Where Y.Field2 = ‘Something’
    So I highlight the statement from the select * and run it to verify the results.
    We all have our ways, the transaction is a another way that will work.

    Reply

    • K. Brian Kelley
      Jun 13, 2016 @ 07:52:48

      I always do that, too, but consider that if you don’t wrap the update/delete in an explicit transaction that you manually verify before applying the COMMIT, you can’t undo the command of your SELECT shows you made an error.

      Reply

  2. Ryk McDorman
    Jun 13, 2016 @ 10:28:31

    I learned this lesson the hard way, after changing an acknowledgement number on millions of wire transfer records at the bank that I work for. It gave me the opportunity to test my point-in-time recovery procedure! Needless to say, any update query I run now has a BEGIN TRAN and COMMIT TRAN.

    Reply

  3. jGm
    Jun 13, 2016 @ 10:41:47

    I think this is a good idea.

    I did have a BEGIN TRAN before a DELETE FROM … which I highlighted without highlighting the WHERE clause nor the BEGIN TRAN. On a production database. Rats. Humiliated myself in front of the DB Admin and asked for restoral of the table. No actual harm done except to my ego.

    Even though this episode remains seared in my mind, yes, I’ll take the extra steps to be triple sure. Rather than doubling up on the mistake.

    Reply

  4. CarlT
    Jun 13, 2016 @ 15:02:38

    Like Jay, I will embed a “–select” in my T-SQl as I always want to verify the data I’m about to corrupt…

    Reply

  5. Glyn Radcliffe-Brine
    Jun 13, 2016 @ 15:32:02

    Once upon a time many years ago (SQL Server 2000) I was caught out by a missing WHERE. Since then I always start any DELETE statement with DELETE WHERE. Then I fill in the blanks. So far it’s saved me from any further disasters.

    Reply

  6. Ryan
    Jun 13, 2016 @ 16:51:23

    I usually write a SELECT then transform it into a DELETE, but I like this idea better.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: