THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Worst code I've written in a while

Here's a nice, compact bit of WTF-ery I had to write for a prod issue today:


UPDATE TOP (1) dbo.someTable SET field3 = 'NEW'
WHERE field2 = 'NEW' AND field3 = ''


Can you guess from the code what awesomesauce issues I was working around?

This was a reminder for me that sometimes there is time to do it right, but sometimes you just have to do it now. I need that lesson sometimes, as I tend to be a perfectionist.

If you are trying to do it right, please don't do what I did. I am off to wash my hands now :-).

Published Thursday, May 20, 2010 11:44 AM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



James Luetkehoelter said:

ROFLMAO...but true, sometimes you just have to get it done...well put!

May 20, 2010 2:04 PM

Jeff Rush said:

Hopefully you needed both Fields 2 and 3 to equal NEW for those entries and the issue wasn't that the value was just placed in the wrong column.

May 20, 2010 2:22 PM

merrillaldrich said:

Yes, both columns needed to have the value 'NEW', but only one did. WTF #1!

It's also worth noting that I only changed the table name when posting here. Those column names are the real column names from the real system. WTF #2!

May 20, 2010 2:36 PM

Bob Probst said:

I'm going to guess that you where having to deal with some roll-your-own row level locking.  If so, I'm very sorry.

May 20, 2010 2:40 PM

Chris J. Breisch said:

Yes, I'm guessin that Bob is correct, that there's some row level locking that you were having to deal with.

Either that or an extremely large table and you were afraid of creating a table lock against it while doing a simpler update.

May 20, 2010 2:55 PM

Tom Hodson said:

perhaps a trigger on the table that has been written so that it expects only one row to be updated at a time?

May 21, 2010 5:04 AM

ALZDBA said:

Payed per executed statement ? ;-)

May 21, 2010 7:16 AM

merrillaldrich said:

Great sleuthing :-). Tom got it, but the other ideas also would fit. Triggers that fail on more than one row are ... well, not awesome. That said, I do like ALZDBA's idea, and I'll be sure to look at that notion carefully, and see how I can make that work.

Adding to this joy, this DB serves an app that can't set default values sensibly for new rows AND won't allow defaults to be set in SQL Server either - it sends in an empty string every time. Super!

May 21, 2010 1:23 PM

David Markle said:

Just promise me that if you check that code in you will give it a comment :)

May 23, 2010 10:36 PM

merrillaldrich said:

Oh, it has a comment all right :-)

May 27, 2010 12:56 AM

Leave a Comment


This Blog


Privacy Statement