October 30, 2013 at 9:37 am
steve.jacobs (10/30/2013)
L' Eomot Inversé (10/30/2013)
Not a really bad query, but not really good either. It's fallen into the trap of assuming that the order in which rows in the heap are scanned is fixed, as pointed out by Venoym. It's also a bit cluttered - rather a lot of complexity to discover that 41 is not between 1 and 12 inclusive, most of that select statement is pure distraction.Very true. However, this is just a small example of the bad query design I have inherited. I wanted to show a small example of what I deal with everyday from our legacy systems. But still fun to try to figure out "bad" query results.
*shutter* 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 30, 2013 at 10:43 am
steve.jacobs (10/30/2013)
L' Eomot Inversé (10/30/2013)
Not a really bad query, but not really good either. It's fallen into the trap of assuming that the order in which rows in the heap are scanned is fixed, as pointed out by Venoym. It's also a bit cluttered - rather a lot of complexity to discover that 41 is not between 1 and 12 inclusive, most of that select statement is pure distraction.Very true. However, this is just a small example of the bad query design I have inherited. I wanted to show a small example of what I deal with everyday from our legacy systems. But still fun to try to figure out "bad" query results.
Ah, yes. Inheriting bad code happens a lot. You have my sympathy.
Of course it could be worse. It gets really bad when the architecture is utterly wrong, the design is just as bad, the code is even worse, the tables mostly aren't even in 1NF (columns containing lists of identifiers, tables with no keys), tables with artificial keys that are completely unrelated to the data in the table, indexes that can never be used, most SQL generated dynamically by badly written VB or C++, blank SA passwords, no logins other than SA and domain admins, no backups even for mission critical databases with 24/365 uptime requirement, and so on ..... and then people wonder why you think there's a problem with the stuff you've taken over.
Tom
October 30, 2013 at 11:08 am
venoym (10/30/2013)
query returned is not garaunteed an order unless an explicit order by is stated.
Yep - that's exactly why I got it wrong :crying:. I guess the correct answer is the one given with " (probably)" appended.
October 31, 2013 at 10:19 am
I don't know, scanning duff code in qotd for the sake of it? You'd probably just as likely find that the output would be ordered.
selectReplace( convert(varchar,
CAST( CAST(a AS VARCHAR(8)) AS DATE) ,1) ,'-','/') + ' ' + SUBSTRING(a,9,2) + ':00' AS dt
FROM#temp
WHEREisdate(a) = 0
anda != ''
order by 1
Max
November 4, 2013 at 11:22 pm
Good question
November 6, 2013 at 8:43 am
Good question, thanks!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply