THE SQL Server Blog Spot on the Web

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

Denis Gobo

I have a question for you on DB development blunders!

Last week Kalen Delaney wrote Did You Know? I have a question for you on DBA Blunders! I though the comments were very interesting, S we did the DBA part but what about developers? What are some of the worst blunders you have seen?
Here are a couple of things


Starting a begin tran, then some insert/update command, never commiting but minimizing the window.


Here is my all time favorite, can you reduce this by 90%?


declare @Token int
select
@Token = 51234567


declare @val int


if
left(@Token,1) = 1
select @val = 1
else if left(@Token,1) = 2
select @val = 2
else if left(@Token,1) = 3
select @val = 3
else if left(@Token,1) = 4
select @val = 4
else if left(@Token,1) = 5
select @val = 5
else if left(@Token,1) = 6
select @val = 6
else if left(@Token,1) = 7
select @val = 7
else if left(@Token,1) = 8
select @val = 8
else if left(@Token,1) = 9
select @val = 9
else if left(@Token,1) = 0
select @val = 0

select @val


Actually we put that on the whiteboard after we found it in our code and every time the developer wanted something we teased him about it...Oh you mean like that (pointing to the whiteboard)
What about changing the datatype from smallint to int in the table but keeping the params smallint in the proc.....mmmm why do I get a conversion in the execution plan?

So let's here some of what you have seen others write, we all know we couldn't write stuff like that ourselves right? :-)

Published Wednesday, May 21, 2008 1:37 PM by Denis Gobo
Filed under: ,

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

Comments

 

AaronBertrand said:

The classic one that bites me and colleagues all the time:

DECLARE @i INT;

SET @i = 1;

WHILE @i < 10000

BEGIN

   -- do something here, and forget SET @i = @i + 1;

END

May 21, 2008 12:56 PM
 

Denis Gobo said:

Aaron, that is the reason they added the Dedicated Admin Connection  :-)

May 21, 2008 1:00 PM
 

AaronBertrand said:

I really wouldn't be surprised if that was on the top of the list.  ;-)

May 21, 2008 1:30 PM
 

Alexander Kuznetsov said:

Denis,

there are hundreds of good examples on worsethanfailure.com

May 21, 2008 2:55 PM
 

Haywood said:

I saw a "purge" process once that went something like this...

SELECT *

INTO ##_Temp

FROM tbl_TransactionLog

DELETE FROM tbl_TransactionLog

INSERT INTO tbl_TransactionLog

SELECT * FROM ##_Temp

This was the result from a DBA that was asked to purge the tbl_TransactionLog and leave only records <= 7 days old in the table. No it wasn't me, but I had to fix it...

May 21, 2008 4:58 PM
 

dbaduck said:

What about the 100 KB update trigger for auditing that declares variables for all columns in the tables affected and declares a cursor of the inserted table and gets the deleted table columns into variables and compares each field variable to each other to find out which ones changed and then proceeds to use a stored proc to add to the log that begins with sp.

Execution plan > 1 MB, executions off the charts and all this on a small 32bit box.  

Now that is performance.

May 21, 2008 6:00 PM
 

dweh said:

isnull((select top 1 'Yes' from hold h (nolock) where h.invoiceid = i.invoiceid and h.removeddatetime is null and h.code = 107944 order by h.onholdid desc),'No') = 'Yes'

A coworker found this gem in a where clause one day.

It was right alongside:

i.invoicedatetime > (select convert(datetime,cast(datepart(mm,getdate())as varchar) + '/' + cast(datepart(dd,getdate())as varchar) + '/' + cast(datepart(yyyy,getdate())as varchar) + ' 07:00:00'))

May 22, 2008 4:46 PM
 

ArjenStins said:

A case of implicit casting?

BOL states:

LEFT ( character_expression , integer_expression )

and returns a varchar or nvarchar.

But in this case we use it on a variable which is declared as integer. And more remarkable is the fact that it returns a integer  too.

So this is valid

declare @Token int,@val int

select @Token = 51234567

select @val = left(@Token,1)

-- @val wants an integer, but LEFT returns varchar/nvarchar (accoording to BOL)

select @val

May 23, 2008 2:29 AM
 

Denis Gobo said:

I forgot one of the most common ones,

trigger doesn't handle multi-row statements

May 23, 2008 3:11 PM
 

Denis Gobo said:

and of course @@identity instead of scope_identity when inserting in a table with a trigger which inserts into another table

May 23, 2008 3:11 PM
 

henry said:

declare @Token int

select @Token = 51234567

select left(@Token,1)

July 22, 2008 8:00 AM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement