I've always wondered ... 1

[Sheco]

I've always wondered how the database determines the order of rows returned by a SELECT statement when there is no ORDER BY clause.

Suppose a table "Animals" with 1 column, and three rows:[tt]
__CommonName__
Dog
Cat
Bird
[/tt]

Suppose the following SELECT statment:[tt]
SELECT CommonName FROM Animals[/tt]


The SELECT statement has no ORDER BY clause so why is the data always returned in the same order?

How is the order determined by the database?

If is it the order in which the rows were added, does the database keep some hidden field value to account for this?

 

[AlexCuse]

Indexes play a large part in it. If you play around with some temp tables doing stuff like this:

[COLOR=blue]declare[/color] @t [COLOR=blue]table[/color](i [COLOR=blue]int[/color])

[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] 1
union all [COLOR=blue]select[/color] 3
union all [COLOR=blue]select[/color] 137
union all [COLOR=blue]select[/color] 2

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @t

[COLOR=blue]declare[/color] @t1 [COLOR=blue]table[/color](i [COLOR=blue]int[/color] [COLOR=blue]primary[/color] [COLOR=blue]key[/color] [COLOR=blue]clustered[/color])

[COLOR=blue]insert[/color] @t1
[COLOR=blue]select[/color] i [COLOR=blue]from[/color] @t

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @t1

you will see some differences.

If you read this thread, it has some interesting info on default sort orders.

http://www.tek-tips.com/viewthread.cfm?qid=1317577&page=3

I find it is best to just use an order by whenever order is going to matter in your result set.

This make things any clearer, or just muddy the waters?

Alex



Ignorance of certain subjects is a great part of wisdom

 

[Crowley16]

it's in the order they were entered, unless you've got a clustered index in there somewhere...

--------------------
Procrastinate Now!

 

[monksnake]

It's by the clustered index, if one exists.

After that, I believe its by database "pages".

It looks through each "page" and writes down its content.



<.

 

[Sheco]

AlexCuse: I read that linked thread and found it very interested, especially the link at the bottom:

http://sqlblog.com/blogs/hugo_korne...les_2D00_vs_2D00_Stones_2D00_explanation.aspx

Crowley16 + monksnake: I am having some difficulty understanding your postings. I was under the impression that a clustered index defines the physical order on the disk. So I interpret those posts as follows:
[blue]The records will be returned in the physical order on the disk, unless they are returned in the physical order on the disk[/blue]

Clearly I am not understanding the finer details.

 

[Crowley16]

essentially, that's exactly it, the only note is that with a clustered index, it will actually keep the records on the disc in order...

of course, that's only assuming you've got a simple query like that, if you've got a complicated query which doesn't use a straight forward table scan, then this might not be the case...

--------------------
Procrastinate Now!

 

[gmmastros]

Crowley,

I think you are missing the point. The order is determined by whichever index is used. Sometimes it is a clustered index, but other times it's not.

Since it would be nearly impossible to predict the index that is used, you are much better off using an Order By in the query.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Lamprey13]

Just to disspell some mis-information. If you do not specify an ORDER BY there is NO guarantee what order the data will come back in regardless of indexes.

 

[ptheriault]

of course, that's only assuming you've got a simple query like that, if you've got a complicated query which doesn't use a straight forward table scan, then this might not be the case...

Crowley,
You may not know this but if you have a clustered index then you will never get a table scan. You may get a clustered index scan but never table scan. You only get table scans on heap tables. A heap is a table without a clustered index.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ESquared]

Sheco: a table doesn't have to have any index at all, which is what Crowely1 meant by "it's in the order they were entered, unless..."

But even with a clustered index there is no guarantee. In a busy system, if two requests come in for data from a table, the later request may "piggyback" on the earlier other one and return data seemingly out of order, data that is already being read to supply the earlier request. I don't remember the exact term for this.

So: never, ever count on an ordering unless you have an order by clause. Not even if you test it 2000 times and six ways from Sunday and it's always the same.

 

[mrdenny]

Contrary to popular beleif the clustered index does NOT ensure the order of data being returned. We had a thread a while back in which we covered this thread183-1272512.

ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified. ORDER BY must be used in any SELECT statement for which the order of the result set rows is important.

A Clustered Index does not guarantee sorted results. A Clustered Index specifies how the data is persisted to disk, ONLY. A Clustered Index NEVER guarantees how results (in what order) are returned to a client. Only an explicit ORDER BY clause guarantees a sort. The physical operators that are in an execution plan can reorder data as is appropriate. The SORT operator is only included in an execution plan when ORDER BY is used.The fact that results must be EXPLICITLY sorted via ORDER BY is an ANSI SQL and a Relational Database 101 issue. We will NEVER "get sorted results on the basis of Clustered Index". We MUST use an ORDER BY clause.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com