Index Key Column Order

I have created a joining table for many-to-many relationship.

The table only has 2 cols in it, ticketid and groupid

typical data would be

groupid    ticketid
20         56  
20         87
20         96
24         13
24         87
25         5

My question is when creating the composite key should I have ticketid followed by groupid

CONSTRAINT [PK_ticketgroup] PRIMARY KEY CLUSTERED 
    (
        [ticketid] ASC,
        [groupid] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

Or the other way, groupid followed by ticketid

CONSTRAINT [PK_ticketgroup] PRIMARY KEY CLUSTERED 
        (
            [groupid] ASC,
                    [ticketid] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
        ) ON [PRIMARY]

Would searching the index be quicker in option 1 as the ticketid has more chance of being unique then the groupid and they would be at the start of the composite key? Or is this negligible?

This all depends on how exactly you are retrieving data from this clustered index. The clustered index will be sorted by the leading key column, followed by sorting on each subsequent key column as defined in the index definition. The best way to see this is through an example.

Test Object Setup

use TestDB;
go

if exists (select 1 from sys.tables where name = 'TestTable2')
begin
    drop table TestTable2;
end
create table TestTable2
(
    groupid int not null,
    ticketid int not null
);
go

insert into TestTable2(groupid, ticketid)
values
    (20, 56),  
    (20, 87),
    (20, 96),
    (24, 13),
    (24, 87),
    (25, 5 );
go

Test 1 (groupid, ticketid) Key Column Order

Now let's create the clustered index defined on the key columns (groupid, ticketid):

alter table TestTable2
add constraint PK_TestTable2
primary key clustered (groupid, ticketid);
go

Now we want to look at how this data is stored in the page:

dbcc ind('TestDB', 'TestTable2', 1);
go
-- FileID:1 PageID:310

if exists (select 1 from tempdb.sys.tables where name like '#DbccPage%')
begin
    drop table #DbccPage;
end
create table #DbccPage
(
    ParentObject varchar(128) null,
    Object varchar(128) null,
    Field varchar(128) null,
    Value varchar(128) null
);
go

insert into #DbccPage
exec ('dbcc page(TestDB, 1, 310, 3) with tableresults;');
go

select
    Object,
    Field,
    Value
from #DbccPage
where ParentObject not in
(
    'page header:',
    'buffer:'
)
and Object not like 'memory dump%';

We see that it is in fact sorted by groupid first, then ticketid. This will result in the follow query being an index seek:

-- this is an index seek
select *
from TestTable2
where groupid = 25;

And this query will be a scan because it can't seek on ticketid:

-- this is an index scan
select *
from TestTable2
where ticketid = 13;

Test 2 (ticketid, groupid) Key Column Order

But now let's change it up and swap the order of key columns:

alter table TestTable2
drop constraint PK_TestTable2;
go

alter table TestTable2
add constraint PK_TestTable2
primary key clustered (ticketid, groupid);
go

Now let's take a look at how the data is on the index page:

dbcc ind('TestDB', 'TestTable2', 1);
go
-- FileID:1 PageID:308

if exists (select 1 from tempdb.sys.tables where name like '#DbccPage%')
begin
    drop table #DbccPage;
end
create table #DbccPage
(
    ParentObject varchar(128) null,
    Object varchar(128) null,
    Field varchar(128) null,
    Value varchar(128) null
);
go

insert into #DbccPage
exec ('dbcc page(TestDB, 1, 308, 3) with tableresults;');
go

select
    Object,
    Field,
    Value
from #DbccPage
where ParentObject not in
(
    'page header:',
    'buffer:'
)
and Object not like 'memory dump%';

Now we see that the data is sorted by ticketid first, then groupid. This will change the behavior of our test queries as follows:

-- this is an index scan
select *
from TestTable2
where groupid = 25;

-- this is an index seek
select *
from TestTable2
where ticketid = 13;

Summary

So as you can see above, it boils down to your workload and the kind of queries that are hitting this clustered index. It's the different between being able to seek based off of the WHERE clause (or a JOIN) or scan depending on the sequential ordering of the key columns and how they are defined.

StackExchange Database Administrators Q#33833, answer score: 8