Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ray-SQL
Starting Member
18 Posts |
 Posted - 2010-03-17 : 14:37:58
|
I am trying to tune a query. The results I ended up with is essentially the same as what is in production, except for ordering, and the sequence does matter.This is simplified code create table #ordertest (id int identity, effectivedate datetime, loadorder int, workordernum int) insert into #ordertest select '03/13/2010', 3, null union all select '03/13/2010', 3, null select * from #ordertest order by EffectiveDate, LoadOrder, WorkOrderNum so in my code it would always return ID1 followed by ID2 but the data in production is ID2 followed byID1. Then I rerun the same select statement that populated the production table, it too returns the order as ID1 followed byID2, not ID2 followed by ID1 as the data is.Question: when all the columns in the "Order By" clause has same values for two rows, is the order random? I did see this in BOL quote:
When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.
But this is a Insert into...Select...Order By scenario.I want to figure out why the same exact query seems to have generated two different orders (it is the final select before insert). I wanted to see if there is something special about two rows that have with identical values in sorted columns would produce unpredictable results or if null is a factor.How to ask good questions that get answers:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxRay Dai |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-03-17 : 14:48:43
|
quote:
Originally posted by ray-SQLQuestion: when all the columns in the "Order By" clause has same values for two rows, is the order random?
Yes. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-17 : 14:50:36
|
The order is not reliable in this case - you should accept this as a fact.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-17 : 14:51:04
|
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ray-SQL
Starting Member
18 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 15:51:13
|
I don't think you were snipped WebFred, because I think "unreliable" (or maybe "unpredictable") is a more accurate description than "random".What I mean is that most of the time the sequence will be the same (usually any Clustered Index will be used as the tie-break, or the physical order on disk). Once in a blue moon it will be different (another query already got 25% of the rows into cache, and they are delivered first). I think it would be much better if it was truly random - i.e. different every time - you would then have a chance of realising that there was no guaranteed / repeatable order. But because it is "usually the same" that means that in DEV you will probably never see a different order, and all your tests will work just fine, and you will be unaware that you could get a different order. That annoyance will only happen once you go Live and put it into Production  Where an ORDER BY clause does not guarantee a unique, repeatable, sequence we always (and I mean always) add the PKs columns to the end to force the order. Guaranteed repeatable ordering.Trouble is, there is nothing that tells us if we forget to do this . And when it breaks in production we have no means of reproducing the problem - it just appears to be a glitch. So we spend HUGE amounts of time trying to work out what happened.On my WishList is to either have randomised ordering of non-repeatable ORDER BY clauses, or a Warning that alerts to the any that are not repeatable. |
|
|
|
|
|
|
|
|
Results of "Order by" when the columns are same
How to ask good questions that get answers: