Re: Compound Primary Key - order not as expected

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

.

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)
  • Re: Storing Users/Groups
    ... websites can interface with this setup. ... > returns @outtable table (root_node int, group_key int, primary key ... > --40 members are members of 50 ... PRIMARY KEY, ...
    (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)