Posted by Robert Klemme on 04/26/06 14:10

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

[Back to original message]