Re: Compound Primary Key - order not as expected

John Rivers wrote:
Hello,

when a clustered index is present the records *are* physically ordered
on disk to match the index

that is the whole point of a clustered index

and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)

Um. No. I've seen it return them out of order with only a few hundred
rows. As soon as the table is occupying more than one page, the query
optimizer *can* decide to produce a parallel plan. You'll see the
result as chunks of output which are in clustered index order, but no
deterministic ordering between the chunks. e.g. it'll look like:

1
2
3
4
5
11
12
13
14
15
6
7
8
9
10

The *only* way to guarantee the order of output is to put an order by
clause on your select statement.

Damien

.

Relevant Pages

  • Re: Do I care about this?
    ... > the order of the clustered primary key ... The clustered index does not indicate the ON DISK order. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Using a character GUID for the primary key - Any issues to know ab
    ... a clustered index ensures that only one disk will ever be used - the disk ... using Identity stops you from later using partitioning if you need to in SQL ...
    (microsoft.public.sqlserver.server)
  • Re: Left Join and Order by
    ... the way the table is ordered on disk is also logically when it has a clustered index. ... determination is made at insert or update time, ... Check out my upcoming book: ASP.NET: Building Ultra-Fast and Ultra-Scalable ...
    (microsoft.public.sqlserver.programming)
  • Re: Compound Primary Key - order not as expected
    ... the physical order on disk may be yet another one. ... data in the order of the clustered index ... This may have been true by chance for SQL Server up version 6.5. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Compound Primary Key - order not as expected
    ... John Rivers wrote: ... data in the order of the clustered index ... David Portas, SQL Server MVP ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)