Best Practice for PK in SQL Server

Question

I have been wondering what the best practices or ramifications are in setting up the PK in a M2M table in SQL Server. For instance:

I have 2 tables

• Users
• Roles

I am making a new table

• UserRole

Which has 2 fields RoleId & UserID

now should I

1. create a UserRoleID as the PK and make UserID and RoleID the FKs
   • make the PK UserID AND RoleID and set them as FKs
   • something else

I would like to know the performance issues with each of the options and what the recommended best practices are.

Answer 1

Standard procedure for these cases is to have two indexes. The unique PK is the two fields composite, with the field with the greater cardinality first, i.e. UserID; and the second index with just the secondary field (i.e. RoleID).

Then cluster on whichever is likely to be involved in more multirecord result sets (i.e. if querying for multiple roles per user, or multiple users per role).

Answer 2

Declare the PK as (UserID, RoleID). (Note: the order is important)

Declare UserID as an FK with the reference to the Users table. Declare RoleID as an FK with the reference to the Roles table.

With any luck, your DBMS will give you a composite index on (UserID, RoleID) in that order.

With any luck this will speed up joins between users and roles. A good DBMS will give you a merge join for a join with no restrictions other than the join condition. A three way join should run pretty fast as well, assuming the number of roles is small.

When you join UserRoles and Roles, without joining in Users, you may find it's disappointingly slow. How often do you do that, and how important is speed in this case? If it is important, you can create an index on just RoleID.