CODEWITHSUNDEEP
sqlsql-serversql-server-2008indexingkey
davey
davey

Reputation: 1564

Composite Keys SQL server

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?

Upvotes: 1

Views: 663

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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).

Upvotes: 0

neelsg
neelsg

Reputation: 4842

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.

Upvotes: 2

Related Questions