Compound Primary Key - order not as expected

[John Rivers]

Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?

[Robert Klemme]

John Rivers wrote:

Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk
This is not wrong at all. As long as you do not have an "ORDER BY"
clause the RDBMS is free to return records in *any* order.
it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
Again: you have to adjust your expectations.
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?

Yes.

Cheers

robert

[Tom Moreau]

Order is not guaranteed unless you include an ORDER BY. This is by design.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"John Rivers" <fi*****@btinternet.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...
Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?

[--CELKO--]

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

You need to read a book on RDBMS; you are still locked into a file
system mind set.

[John Rivers]

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)

this can easily be proved by watching the Execution Plan

the issue i am trying to highlight concerns the order of the records on
disk when a *compound* clustered index is present

i have seen cases when it is not as expected

maybe you can enjoy reading that RDBMS book :-)

best wishes,

john

[Damien]

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

[David Portas]

John Rivers wrote:

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

Not true at all. As Joe says, tables are not logically ordered. There
is no guarantee that any queries will match the physical order on disk
or in a clustered index.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Hugo Kornelis]

On 28 Apr 2006 05:41:26 -0700, 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
Hi John,

Correct.
and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)
Incorrect. Damien already pointed out the risk of parallellism.

Another potential issue is an optimization technique MS employs called
"piggybacking" - if a query on another connection is in the middle of a
tbale scan on the table you need, the DB will use the values coming in
for your query as well, then (when the first query's table scan is
finished) restart the scan from start up to where it started to
piggyback. The results would be like 6 - 7 - 8 - 9 - 10 - 1 - 2 - 3 - 4
- 5

This is almost impossible to reproduce in a test environment, but it
MIGHT happen intermittently in a heavily used production DB. Tough lluck
if your app expects the rows to be in order, even without ORDER BY.
the issue i am trying to highlight concerns the order of the records on
disk when a *compound* clustered index is present

i have seen cases when it is not as expected

How did you "see" those cases? Using a query reallly doesn't prove
anything. Did you issue DBCC PAGE commands to inspect the actual
contents of the index and data pages?

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

John Rivers (fi*****@btinternet.com) writes:

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
Actually, they are ordered if you follow the page links. But if pages
are in disorder, the physical order on disk may be yet another one.
and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)

No. This may have been true by chance for SQL Server up version 6.5. It is
definitely not correct for SQL 7 and later.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[John Rivers]

Thanks for your knowledgable answers

I will check out DBCC PAGE