SQL non-clustered index

Question

I have a table that maps a user's permissions to a given object. So, it is essentially a join table to 3 different tables. (Object, User, and Permission)

The values of each row will always be unique for all 3 columns, but not any 2.

I need to create a non-clustered index. I want to put the index on the foreign keys to the object and user, but I am wondering if I should put it on all 3 columns.

Answer 1

If you have some doubts, formulate the query(ies) you intend to execute against these tables, and run the SSMS Query Tuning Wizard. That should help you get started in the right direction.

One thing to consider is the number of rows in these three tables. If the row counts will be small, it might not even be worthwhile adding indexes. A table scan would probably be done anyway.

Answer 2

"The values of each row will always be unique for all 3 columns"

You might be interested to know that SQL Server unique constraints are implemented as indexes. So if you have (or want) a constraint backing up that unique-claim of yours, you already have an index on all 3.

CREATE UNIQUE NONCLUSTERED INDEX idx_unique_perms ON UserPermissions
(
    ObjectId ASC,
    UserId ASC,
    PermissionID ASC
)

If you make one, just remember to order your columns for high selectivity.