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.