What is the default record sort order for a select

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What is the default record sort order for a select

Author

Topic

jhilb007
Starting Member

17 Posts

Posted - 2007-01-03 : 15:06:33

Does anyone know, or have reference to documentation that describes the default sort order for records retrieved for a select with no ORDER BY clause?

I mean, let's say I have a table with a single column, call it Data1. This is the only column on the table.

If I do

SELECT Data1 FROM MyTable

What order is the Data1 in? I am being told it's the same order in which the records were inserted. Usually, this seems to be the case, but every once in a while it appears not to be.

Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-03 : 15:14:29

There is no default order. If you want a particular order, you must have an ORDER BY clause.

CODO ERGO SUM

jhilb007
Starting Member

17 Posts

Posted - 2007-01-03 : 17:46:18

That is what I thought. Is there any reference materials to proof this?

Problem is, I wrote a stored procedure to be submitted to my dba. This procedure produces output to go to an output file (physical). Because they wrote a general routine to handle the scheduling I must conform to their specs, which say, single table, single column.

They simply do a "SELECT Data1 FROM MyTable" and push the results to an output file.

I told them without an ORDER BY clause there is no telling what the order of the data within the output will be.

They tell me as long as it's inserted to the table in the correct order that it'll come out in the same order.

I know this not to be true. But I have to find some documentation saying so that I can tell them to go examine and see for themselves.

J

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 17:55:53

your dba probably knows better. must specify order by if u want to guarantee sort order

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 17:58:14

http://www.informit.com/articles/article.asp?p=174344&rl=1

If unsorted, data will typically be displayed in the order in which it appears in the underlying tables. This could be the order in which the data was added to the tables initially. However, if data was subsequently updated or deleted, the order will be affected by how the DBMS reuses reclaimed storage space. The end result is that you cannot (and should not) rely on the sort order if you do not explicitly control it. Relational database design theory states that the sequence of retrieved data cannot be assumed to have significance if ordering was not explicitly specified.

jhilb007
Starting Member

17 Posts

Posted - 2007-01-03 : 18:53:01

Thanks. No, my dba is telling me I cannot use a sort order. I am the one insisting I need to use a sort order to ensure the order.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 18:57:47

Well if you have a clustered index on the table, it'll be sorted according to that index if you don't specify an ORDER BY.

Tara Kizer

jhilb007
Starting Member

17 Posts

Posted - 2007-01-03 : 19:03:48

Ah, I didn't think of that. However, it's just a straight table, no indexes.

See situation is that recently the stored proc produced some results out of order (even though they were inserted into the table in the correct sequence). I told the dba, see, I told you it would eventually happen. They ran the same proc again, same data, immediately; and then the results were in the order as inserted.

So like maybe 99 out of 100 runs winds up being output correctly (in the order in which the rows were inserted). But that 1 run just isn't every once in a while.

Ah well, they won't listen to me, I'll have to live with it.

Thanks All.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-01-03 : 19:38:21

quote:
Originally posted by jhilb007

Thanks. No, my dba is telling me I cannot use a sort order. I am the one insisting I need to use a sort order to ensure the order.

Your dba is dead wrong.
I could use much stronger words, but being civil I will restrain.

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-03 : 19:55:11

quote:
Originally posted by tkizer

Well if you have a clustered index on the table, it'll be sorted according to that index if you don't specify an ORDER BY.

Tara Kizer

This is not always the case. SQL Server will deliver the data in the order it finds most convenient. For example, if pages at the end of the table are already in memory and the beginning of the table is not, it can return those first.

Absent an ORDER BY, the order of the result set is unpredictable.

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 20:01:41

I've never seen it not return the data ordered by the clustered index when no ORDER BY was given. I ran a bunch of tests a while ago trying to get it to return out of order as a developer was wanting to exclude an ORDER BY. I tried and tried and tried but no luck. It was always ordered by the clustered index. Do you have any test code I could use to disprove it?

I for one always put an ORDER BY and always require the developers to use an ORDER BY when sorting is importing.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-03 : 20:19:16

quote:
Originally posted by tkizer

I've never seen it not return the data ordered by the clustered index when no ORDER BY was given. I ran a bunch of tests a while ago trying to get it to return out of order as a developer was wanting to exclude an ORDER BY. I tried and tried and tried but no luck. It was always ordered by the clustered index. Do you have any test code I could use to disprove it?

I for one always put an ORDER BY and always require the developers to use an ORDER BY when sorting is importing.

Tara Kizer

I have seen it happen a number of times, but I don't know how to force it to happen.

I guess the fact that I have seen it happen is proof enough for me since a single exception disproves it. Obviously, it doesn't prove it to you, since it you haven't seen it happen.

Maybe it is easier to turn the question around, and ask whoever is asserting that an unordered result set will be in table physical order to prove it. Absent any definitive statement in Books Online that says SQL Server will act that way, how can they prove it? They can't prove it by example, since that would require running all possible queries against all possible databases in all possible server states and examining the output to make sure there is no exception to the rule.

Edit:
I just got it to happen with the following code:


drop table #temp
go
create table #temp(num	bigint	not null primary key clustered)
go

insert into #temp
select top 100 percent number
from f_table_number_range(5000,10000)
go

insert into #temp
select top 100 percent number
from f_table_number_range(3000,4999)
go

insert into #temp
select top 100 percent number
from f_table_number_range(1,2999)
go

select * from #temp

Results:


num                  
-------------------- 
1
2
3
4
...
...
472
473
474
475
476
5476
5477
5478
5479
...
...
9996
9997
9998
9999
10000
5238
5239
5240
5241
...
...
5472
5473
5474
5475
5000
5001
5002
5003
5004
...
...
5233
5234
5235
5236
5237
3476
3477
3478
3479
3480
...
...
4994
4995
4996
4997
4998
4999
3238
3239
3240
3241
...
...
3471
3472
3473
3474
3475
3000
3001
3002
3003
...
...
3233
3234
3235
3236
3237
477
478
479
480
481
...
...
2996
2997
2998
2999

(10000 row(s) affected)

CODO ERGO SUM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 20:39:02

can try to force page split by inserting/deleting data to prove that clustered index cant assure sort order...but why go to the trouble...

rebuild clustered index just b4 query may make it a lot more likely to be in expected order...still...no where clause means no guarantee of order. your dba ought to know that

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 20:39:40

quote:
Originally posted by Michael Valentine Jones
I just got it to happen with the following code:

nice.

i wonder what happens if u rebuild clustered idx and run select. obviously having index BEFORE any data is inserted is different than creating after. anyway, just proves everyones point. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 21:34:24

Thanks Michael! Now if I can only remember the developer who wanted to do this, then I'd be able to show proof positive that we need to use ORDER BYs.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-03 : 21:43:24

quote:
Originally posted by tkizer

Thanks Michael! Now if I can only remember the developer who wanted to do this, then I'd be able to show proof positive that we need to use ORDER BYs.

Tara Kizer

Did it give you the same result on your system?

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 21:53:21

I haven't tested it yet. I was going to do that tomorrow morning.

Thinking back though, I remember that our column was an identity. So I'm going to play around with that too.

Tara Kizer

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 21:56:34

ordered 1--100000 on mine in 3 successive tries. bet enough deleting and inserting will do it tho

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 22:51:35

I am getting results similar to MVJ:

1
2
3
4
..
476
5476
5477
...
10000
5238
5239
...
5475
5000
5001
...
5237
3476
...

and so on.

Completely random. That proves it!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 02:12:12

"They tell me as long as it's inserted to the table in the correct order that it'll come out in the same order."

Bollocks!

Sorry I'm late to the party!

"Do you have any test code I could use to disprove it?"

You may have to remove most of the memory from your mega-servers to cause it to happen Tara!

There was a post here from Paul Randall where he explained how data resident in memory, which only satisfied part of a query, could be "used first" in a query whilst the remainder was being fetched from disk, and this would influence the displayed-order (absent an ORDER BY). (Sorry, tried a search for the post, but I can't find it easily)

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-04 : 08:55:43

I think the explanation for the results from the code I posted is that it is the result of extent splits. A clustered index keeps the data in order within a page, but that does not mean that the pages are in physical order if SQL Server had to split an extent.

Probably SQL Server decides to get the data one extent at a time since no ORDER BY is specified. If the extent is split, page 1, and 10 to 16 of the data may be in extent 1, and pages 2 to 9 in extent 2. If SQL Server gets the data for extent 1 and then extent 2, the data would come back in this page order: 1, 10-16, and 2-9.

I inserted the data out of order to try force extent splits. It looks like Harsh Athalye got exactly the same results. If the table was reindexed after the insert, the select result would probably be in clustered index sequence.

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources