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?
Best Answer
The order of the columns in indexes is critical.
If your
AdministratorUserIdis the first column of the index, then separating the indexes will not improve anything.The index works like an
order byclause, meaning that it is sorted by the first column, then the second, then the third and so on. Because of that, an Index onAdministratorUserIdis functionally equivalent to an index onAdministratorUserIdandName. 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.