SQL Server 2008 - combined index + other separate index on one column

Question

Lately I've been refactoring the SQL Server indexes of the products that I have developed for my employer. One of these products is an online dashboard containing multiple user levels.

Each user (on admin level) can create projects that they can then link to users of the user level below. Each project has its own Name field and also the field AdministratorUserId to keep track of the project admin. I created a combined non-clustered unique index on these two fields, meaning that project names have to be unique per administrator.

Now here's the catch: I am noticing that I often do a select on AdministratorUserId only, and not on Name. According the indexing guidelines, and please correct me if I'm wrong, I am to create a non-clustered index on the field in order to optimize my queries.

My question being - does my existing index on Name + AdministratorUserId already improve my queries or is it advisable to create a separate index besides it? Would there be any downside to AdministratorUserId being present in two separate indexes?

Answer 1

The order of the columns in indexes is critical.
If your AdministratorUserId is the first column of the index, then separating the indexes will not improve anything.
The index works like an order by clause, meaning that it is sorted by the first column, then the second, then the third and so on. Because of that, an Index on AdministratorUserId is functionally equivalent to an index on AdministratorUserId and Name. Note that it's possible in sql server to include columns to a non-clustered index that are non key columns. This will increase the storage size of the index but may improve performance of queries since it will save a lookup operation on columns that are included in the index.

Answer 2

Run this script in your database:

SELECT obj.*,usage.*
FROM sys.dm_db_index_usage_stats AS usage
INNER JOIN sys.indexes as obj
        ON usage.object_id = obj.object_id
WHERE database_id = db_id()

It will provide you with the index usage of your indexes. You can take a look (or filter on it) to see how often your index is used for scans or for seeks. After that you can make you an own picture of your indices.

If you filter always over AdministrationUserId and your index is build name, AdministrationUserId it won't benefit from it.