|
|
Posted by Tom Moreau on 04/26/06 13:57
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" <first10@btinternet.com> wrote in message
news:1146048739.469710.138210@e56g2000cwe.googlegroups.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?
Navigation:
[Reply to this message]
|