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.