|
|
Posted by John Rivers on 04/26/06 13:52
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?
Navigation:
[Reply to this message]
|