March 17, 2010 at 10:57 am
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?
p.s. I did see this in BOL
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.
Thank you.
March 17, 2010 at 11:37 am
Nothing in SQL guarantees sequence except the Order By on the final select.
If you really want to force a specific sequence, you have to include that. Otherwise, all kinds of factors can mess with it. Rows in a table have no inherent sequence.
If you need a specific sequence that way, what you can do is change the ID column into just an integer, without an identity clause, and use Row_Number() to force that to have the sequence you want, then do your final select based on that column. I've done that many times and it works quite nicely.
- 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
March 17, 2010 at 12:13 pm
Thanks for the reply. That makes sense. I am not so much want to "force" a certian order as 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.
March 17, 2010 at 12:19 pm
Simply put, SQL does whatever order works best for it's engine, unless you tell it otherwise explicitly with a final Order By.
So it's not really "random", but it's definitely not guaranteed to be predictable.
- 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
March 18, 2010 at 12:22 am
Things are slightly different when an IDENTITY column exists on the table.
Essentially, SELECT...INTO...ORDER BY does not guarantee identity assignment order, whereas INSERT...SELECT...ORDER BY does.
Note that although guaranteed to be ordered, the identity values are not guaranteed to be contiguous.
Whatever the 'insertion order', rows returned by a subsequent SELECT are not guaranteed to respect that order, unless ORDER BY is specified on the outer SELECT, as Gsquared remarked.
March 18, 2010 at 8:17 am
Thanks Paul and Gsquared.
I am not sure I am communicating the problem clearly. It is a Insert..Select...Order By and not a Select Into.
I am running the sameselect statment with the final "order by" that inserted these rows but got different orders than what the same statement inserted into the table.
Conceptually I guess it only make sense that when all the values for "Order By" is the same then the ordering is not guaranteed.
I.e. if I do select from table order by colA, colB
and two rows have these values
name, ColA, ColB
Bob, 1, 2
Joe, 1, 2
then ordering could not be guaranteed.
I've just wanted to see anyone had experience with this or additonal knowledge to confirm this.
Thanks!
March 18, 2010 at 8:20 am
The problem isn't the Insert. It's the final Select.
select * from #ordertest order by EffectiveDate, LoadOrder, WorkOrderNum
should be:
select * from #ordertest order by EffectiveDate, LoadOrder, WorkOrderNum, ID
or:
select * from #ordertest order by ID
Try those, see if you get what you want.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply