Re: Compound Primary Key - order not as expected

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@xxxxxxxxxxxxxx> wrote in message
news:1146048739.469710.138210@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?

.

Relevant Pages

  • Re: Non-modifiable index/field values
    ... key column but not on some one of the primary key columns. ... It's also important to add a UNIQUE constraint on the identity column to ... pk1 int not null, ... select @@rowcount ...
    (microsoft.public.sqlserver.programming)
  • Delete Cascade
    ... Id int identity not null, ... constraint PK_DocumentsPages primary key clustered(DocumentId, ... add constraint FK_DocumentsPages_Documents foreign key (DocumentId) ...
    (microsoft.public.sqlserver.programming)
  • One vs Many Tables
    ... Profile data ... Id int identitynot null, ... Approved bit not null constraint DF_Users_Approved default, ... constraint PK_Profiles primary key clustered ...
    (microsoft.public.sqlserver.programming)
  • Can this be done using a Data adapter??
    ... adapter's update method in this scenario. ... id1 int identity ... Constraint pk_table1 Primary Key, ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: SQL2000 Check Constraints
    ... Are you using this column as part of the primary key?. ... orderid int not null, ... constraint pk_t1 primary key nonclustered ...
    (microsoft.public.sqlserver.programming)