SQL Server creates multiple non-clustered indexes for a single column, having multiple columns in only one index

Question

Suppose I have the following table

• UserID (Identity) PK
• UserName - unique not empty
• UserEmail - unique not empty

What is recommended for maximum performance?

• creating a nonclustered index for UserName and UserEmail separately

OR

• Only one including both columns

Please share your thoughts on why others are preferred.

Answer 1

Another important point to consider is the following: a composite index (consisting of several columns) will be used only if the n leftmost columns are referenced (for example, in the WHERE ).

So, if you have one composite index on

 (UserID, UserName, UserEmail) 

then this index can be used in the following scenarios:

• when you search for only UserID (using only one left column - UserID )
• when you look for UserID and UserName (using 2 columns on the left)
• when searching for all three columns

But this single connection index will never be used to search by

• just UserName is the second column in the index, and thus this index cannot ever be used
• just UserEmail is the third column in the index, and thus this index cannot ever be used

Just remember this - simply because the column is part of the index, does not necessarily mean that searching in only one column will be supported and accelerated by that index!

So, if your usage patterns and your application really should search only for UserName and / or UserEmail (without providing other search values), you should create separate indexes in these columns - only one composite does not have any benefit.

Answer 2

The best way to determine indexes depends entirely on how you use the table. There is no reasonable choice of indexes, just by looking at the table definition.

If your code browses your table with a username or joins your table to another table with a username, then it would be wise to define an index in that column. If your code joins a table with another table using two columns (username and usermail), then it would be wise to define an index for these two columns. Since all of your columns are defined as unique, I hardly believe it will be so, so you won't need multiple column indices in this table.

Perhaps there are some tips for using multiple column indexes. Multiple column indexes are also used for filters that match the index partially, but with conditions.
Example: If you determine the index of two columns on the username and usermail (in the given order), you will get a performance boost in search queries that filter both columns (username and usermail). With this index, you will also get performance gains in filters that use the username only because it is the first column of the index, but not in the search through usermail, because the second column of the index cannot be used alone. Rule: An index can be used for filtering with exact match columns, or filtering with a subset of columns that correspond to subsequent upper columns in the index definition.

Answer 3

Please share your thoughts on why others are preferred.

It depends on what you do.

See, the index is used only from left to right. So, indes on UserID; UserName is useless if I select filtering only with UserName.

As a rule, I would suggest three indicators here:

• Uniuqe index clustered in UserID as primary key.
• Unique index in UserName, not clustered.
• Unique pointer to UserEMail, not clustered.

The reason is not completely in performance, but:

• You will need the first as the primary key for the forein key relationship.
• You need two others to handle unique constraints correctly - there is no way to do this without indexes.

In addition, you need the flexibility of searching with UserName AND UserEMail, which means that they cannot be combined only.

Performance really comes last - for the main reasons All these indexes can contain all additional fields (not as part of the index, but as included columns). But in fact there is no other reasonable way of working with this table, if you are not apart from several registrations for the same user.