Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
In a joining table like this, one usually has both indexes. Now, which one of the two should be clustered, is an interesting question. –  ypercube Jan 30 '13 at 12:33
1  
It depends on what query you are using (the most). Do you want to find groups belonging to a ticket or do you want to find tickets belonging to a group? If you only want to find one row given the values of the primary key you should have the most selective column first. @ypercube In the case with two indexes I don't think it would matter which one is clustered or...? The second is covering so it should be enough. –  Mikael Eriksson Jan 30 '13 at 12:54
    
@Mikael: I meant it might matter (and be an interesting question) for INSERT/DELETE/UPDATE efficiency and for fragmentation. –  ypercube Jan 30 '13 at 13:05
add comment

1 Answer

up vote 5 down vote accepted

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%';

enter image description here

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;

enter image description here

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;

enter image description here


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%';

enter image description here

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;

enter image description here

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

enter image description here


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.

share|improve this answer
    
Awesome explanation, thank you very much. I though that might be the case. Difficult to say which of the columns will be filtered on most. What I have done is gone for a group id first then created an unclustered index on the ticketid –  davey Jan 30 '13 at 16:11
    
+1 for dbcc ind and dbcc page that I haven't seen before. –  Wojteq Jan 30 '13 at 16:43
    
@Wojteq They are undocumented. See here: blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/… –  Thomas Stringer Jan 30 '13 at 17:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.