Is the result of "Order By" for exact same values random?

Question

Is the result of "Order By" for exact same values random?

Ray Dai

SSCommitted

Points: 1548
More actions
March 17, 2010 at 10:57 am

#143530

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Ray Dai SSCommitted Points: 1548 More actions · Answer 2

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.

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

Paul White SSC Guru Points: 150468 More actions · Answer 4

Things are slightly different when an IDENTITY column exists on the table.

See The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Ray Dai SSCommitted Points: 1548 More actions · Answer 5

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!

GSquared SSC Guru Points: 260824 More actions · Answer 6

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