http://www.sqlservercentral.com/blogs/steve_jones/2009/12/16/someone-needs-an-order-by/

Printed 2014/10/21 07:50PM

Someone Needs an Order BY

By Steve Jones, 2009/12/16

I was checking on the status of my laptop the other day. It died and I shipped it back to Toshiba for repair. I ended up getting this in the status window:

 

laptoprepair

What’s great about this is the last column. I have these dates:

12/02/2009 01:30:00

12/03/2009 21:52:00

12/03/2009 21:52:00

12/04/2009 13:39:00

12/07/2009 20:46:00

12/04/2009 13:41:00

12/04/2009 21:04:00

12/07/2009 18:52:00

12/04/2009 13:38:00

Amazing isn’t it? The most recent repair entries are the5th and 8th entries (of 9). If I was checking for a change and glanced at the bottom record, I’d think nothing had changed.

And in fact, nothing has changed in a week, but that’s another blog.

I’m sure a developer that doesn’t do a lot of database work built this screen, inserting test records, having them appear on screen and thinking things were working. And in limited tests, with small groups of data, the natural order often appears to be holding itself as valid. The natural order is the order that the records are inserted into SQL Server, and often with no indexing, or with a clustered index on a column, in this case the “done” column, they would return in that order.

However most database products don’t guarantee the natural order when you query for records. A fundamental aspect of my SQL systems is that there is no guarantee of order without an ORDER BY clause. Indexes are for performance, and can change. I wouldn’t be surprised if the clustered index for this table were put on the serial number and repair number instead of the date. That’s often how you’d query this data and would make sense. So without an ORDER BY date on this query, which clearly doesn’t exist, you get repair records out of order.

And you annoy customers.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.