November 2, 2009 at 2:50 pm
Pretty simple question - is there any problems if the order of a query is not by default that of the primary key?
I was under the assumption that if there was no primary key, then the order was indeterminate - but, if there was a primary key, then by default the order of a query would be that of the primary key.
Basically, I just want to know if the fact that the query is not ordered by the primary key by default, constitutes a problem with the table/database.
November 2, 2009 at 2:53 pm
By definition, a table is unordered even if you have indexes defined (either nonclustered or clustered). The fact that a result set returned by an unordered query (no ORDER BY clause) does not mean that will always happen. If you need the data returned in a specific order every time it is run you need to include an ORDER BY clause.
November 2, 2009 at 2:54 pm
By definition, if you don't supply an Order By clause in a query, SQL isn't obligated to return it in any particular order at all. It will often be in order of the primary key, but not necessarily. Sequence may be modified by the join engine, by use of a covering index, or any number of other things, if you don't explicitly tell it what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2009 at 2:58 pm
perfect - that's what I wanted to hear. thanks!
and yeah - I know that I should always be using order by if it's necessary for the functionality of the query. I just wanted to be sure that it wasn't being caused by a problem with the indexes or something like that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply