What's the worst SQL query you have ever seen? What managed to get bad?


Seen immediately after I typed and performed it, I'd forgotten the WHERE clause. Now I usually operate a Choose statement first and alter the Choose to Remove once i am satisfied the proper rows is going to be affected.

The classic xkcd obviously:

WHERE name = ROBERT'); DROP TABLE students;--

Worst USE of the SQL query every:

A Choose query that that counts the amount of lines akin to a particular condition, contacted the preventing condition of the for loop.
Something similar to this:

for(int i = 0; i < query("SELECT COUNT .... WHERE ..."); i++)


With no, caused by the query does not change every iteration. Yes I recognize the server will cache the end result.

A person was storing a comma delimited listing of 3 values inside a varchar area (classic ASP application) so that they were built with a saved method that looked something similar to this:

    Field LIKE @Param + ',%'
    Field LIKE '%,' + @Param + ',%'
    Field LIKE '%,' + @Param

It ought to be apparent why it's horrible :)

select * from users where clue > 0;
0 results found.

My very own, that is far to lengthy to publish here -- closing now on 3500 lines

I must really share the culprit by having an absolutely horrible schema. What began off like a simple exercise in pivoting denormalized data with a couple unions converted into an unwieldy nightmare. It's badly looking for repair.

Runner up is:

case datepart(mm,getdate())
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'March'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'July'
when 8 then 'Aug'
when 9 then 'Sept'
when 10 then 'Otc'
when 11 then 'Nov'
when 11 then 'Dec'

You will find no typos for the reason that publish -- that's the way it was written. Thanks, talking to dollars!

I obviously refactored with Choose left(datename(mm, getdate()), 3)

Initially when i first experienced my current job my first project ended up being to create a credit card applicatoin that made clear our license usage data within our computer labs. He was adamant he did not want the after sales database to become stabilized because joins were "too costly." It being my first week, I wasn't capable of argue.

Now, to be able to extract any helpful data in the database, one needs to "undo" the denormalization in each and every query that must extract summaries to get rid of the copied data in every row. Obviously, fundamental essentials only queries which are really used. The thing is lots of nested chooses that might be completely unnecessary when the data were stabilized, for example:

select location, sum(login_time) as total_login_time
    (select location, session_id, max(login_time) as login_time
     from sessions
     where location in ('lab1','lab2') 
           and session_start >= @start_date 
           and session_end <= @end_date
     group by location, session_id) tbl
group by location

Although, the query itself is not particularly ugly -- though some are -- the entire process of needing to jump through hoops each time to undo the unwanted denormalization affects.

The boss is finished, however i do not have time for you to rewrite it...

A PL/SQL (Oracle) saved proc that sorted an effect set utilizing a Bubble Sort. It had been discovered after i and also the DBA were requested to determine a serious performance problem. The developer, an Oracle "expert," had done it for more than per week. He described having a straight face he discovered Bubble Sort in the computer science class. The formula is generally accustomed to illustrate poor performance.

Changed the entire wreck havoc on a purchase BY clause. Performance enhanced by a number of orders of magnitude.