Composite Keys SQL server

Question

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's have more chance of being unique then the groupids and they would be at the start of the composite key? Or is this negligible?

neelsg · Accepted Answer · 2013-01-30 12:52:06Z

2

The difference would most likely be negligible.

It is however recommended for SQL Server that the most selective column be placed fist. If a column with low selectivity is placed first, the Optimizer may determine that your index is not very selective and will choose to ignore it. See this sqlserverpedia.com Wiki Article for more information.

edited Jan 30, 2013 at 12:52

answered Jan 30, 2013 at 12:33

neelsg

4,8425 gold badges37 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

UnhandledExcepSean · Accepted Answer · 2013-01-30 12:52:52Z

0

I would actually create two indexes. Given that ticket IDs are more likely to be unique, the clustered index would be GroupID,TicketID in that order. I would then create a non-clustered non-unique index on TicketID.

The reason being that if you wanted to query based only on group ID, they would logically be contiguous and there would be a block of them. The other index will give you fastest when only TicketID is specified.

I do think it would probably be negligible overall depending on how the data will be queried (i.e. if groupid and ticketid are always provided).

answered Jan 30, 2013 at 12:52

UnhandledExcepSean

12.9k4 gold badges38 silver badges54 bronze badges

Collectives™ on Stack Overflow

Composite Keys SQL server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related