SQL Server creating multiple nonclustered indexes for one column vs having multiple columns in just one index

Question

Suppose I have following table

• UserID (Identity) PK
• UserName - unique non null
• UserEmail - unique non null

What is recommended for the best performance?

• creating non clustered index for UserName and UserEmail separately

OR

• Just one including both the columns

Please do share you thoughts why one is preferable over other.

Answer 1

Another important point to consider is this: a compound index (made up of multiple columns) will only be used if the n left-most columns are being referenced (e.g. in a WHERE clause).

So if you have a single compound index on

(UserID, UserName, UserEmail)

then this index might be used in the following scenarios:

• when you're searching for UserID alone (using just the 1 left-most column - UserID)
• when you're searching for UserID and UserName (using the 2 left-most columns)
• when you're searching for all three columns

But this single compound index will never be able to be used for searches on

• just UserName - it's the second column in the index and thus this index cannot ever be used
• just UserEmail - it's the third column in the index and thus this index cannot ever be used

Just remember this - just because a column is part in an index doesn't necessarily mean that searching on that single column alone will be supported and sped up by that index!

So if your usage patterns and your application really need to search on UserName and/or UserEmail alone (without providing other search values), then you must create separate indices on these columns - just a single compound one will not have any benefit at all.

Answer 2

The best way to define indexes depend entirely on how you will use the table. There is no sensible way of choosing indexes just by looking at the table definition.

If your code searches through your table with username or joins your table with another table through username, than it would be wise to define an index on that column. If your code joins the table with another table using two columns (username and usermail), than it would be wise to define index for those two columns. Since all your columns are defined to be unique, I hardly believe this will be the case so you will not need multiple column indexes on that table.

There might be some additional advice on using multiple column indexes: Multiple column indexes are also used for filters that fit the index partially, but with conditions.
Example: If you define a two column index on username and usermail (in given order), you will have performance gain in searches that filter through both columns (username and usermail). With that index you will also have performance gains in filters that use username only because that is the first column of the index, but not in searches through usermail, that is because the second column of an index can not be used alone.
The rule is: An index can be used for filtering with exact matching columns or filtering with a subset of columns that match subsequent top columns in index definition.

Answer 3

Please do share you thoughts why one is preferable over other.

It depends on what you do.

See, an index is only used "left to right". So, an indes on UserID; UserName is useless if I select filtering by UserName ONLY.

Generally, I would assume three indices here:

• Uniuqe Index, Clustered in UserID, as Primary Key.
• Unique Index on UserName, non clustered.
• Unique Index on UserEMail, non clustered.

The reason is totally not for Performance but:

• You will Need the first as Primary key for forein key relationships.
• You Need the other two to handle unique constraints properly - there is no way to do that without indices.

In Addition, you Need flexibility to seek by UserName AND UserEMail, which means that it is not possible to Combine them only.

Performance really enters last here - for performacne e reasons all of These indices may contain all additional fields (not as part of the index but as included columns. But really, there is no other sensible way to have this table work unless you alow multiple registrations for the same user.