March 30, 2003 at 9:24 pm
Hello, here's an interesting story that might make you laugh...followed by a quick question:
A junior developer forgots to add the ORDER BY clause to a select statement where the order is VERY important (each row contains a command that runs in a batch and of course must be done in the correct sequence)
Now the results all seem to be ordered correctly all through development, unit testing, UAT and two months into the live release. But suddenly it all starts to go wrong!
OK so this is a true story, and the problem is easy enough to fix, but we need to assess the potential damage of previous batches run in the incorrect order....
Does anyone know any rules for the order that SQL server will return results in if no ORDER BY clause is specified?
obviously it seems to order by the primary key (or clustered index) most of the time, but under what conditions would this not be the case, and how frequently could this have happened...
any help will be much appreciated,
Brendon.
Edited by - brendonsmith on 03/30/2003 10:02:58 PM
March 31, 2003 at 2:27 am
Relations per definition are unordered, therefore your results from a query missing an ORDER BY can theoretically come in any order at all. You can never be sure of which order they will come in. Some will argue that if you have a clustered index onthe table where the rows are fetched from they will come in that order, but this is not entirely true since some operations may change this, for instance a hash join. If you don't state an explicit ORDER BY SQL Server will return the rows as they are fetched.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 31, 2003 at 2:41 am
I had a similar problem with temporary tables.
I had an sp which would loop round for each each month for 3 years worth of data and inserted output rows into a temp table. At the end of the proc I output the rows from the temp table.
It worked fine in UAT and in production but every so often the rows were returned out of sequence - certain queries produced the correct results and a few always produced an incorrect sequence.
I had made the assumption that SQL Server would return the rows in the order in which they were inserted into the table but as Chris said this is not always true.
In the end I had to put an identity column on the temp table and include an order by in the final select statement.
At the end of the day, if you want something in a specific order, you must explicitly state it with an order by statement.
Jeremy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply