Will SQL Server use a composite index if only one column is in the WHERE clause?

Question

Let's say I have a table:

CREATE TABLE Users ( Id INT IDENTITY (1, 1), FirstName VARCHAR(40), LastName VARCHAR(40) ) 

Queries are usually called FirstName or LastName, but also in FirstName and LastName.

If I create a non-clustered index for FirstName and another in LastName, then my first two queries are satisfied. Apparently, SQL Server will use index intersection for another query.

Alternatively, if I have indexes on (FirstName) and on (LastName, FirstName), can SQL Server use the second index for queries only for LastName, as well as for queries on both?

Does SQL Server keep index components left to right or right to left? In other words: will it build the key as LastNameFirstName or FirstNameLastName? Or can I choose arbitrarily?

Answer 1

can SQL Server use the index (LastName, FirstName) for queries only for LastName, as well as for queries on both?

Yes, the database will use the index (LastName, FirstName) for queries in LastName. He will not use this index for queries only by first name.

Does index component keep from left to right or from right to left?

Storage is in the B-Tree . Regardless of whether you consider it to be stored from right to left or left to right, this is simply a useful visualization tool and is not related to the actual storage of data.

Answer 2

Yes, if you only query LastName, it should use the index (LastName, FirstName). Thus, it will be used both when querying LastName independently, as well as LastName and FirstName.

The general recommendation is to ensure that the column with the highest selectivity appears first in the composite index, as this gives the greatest benefit / narrows the set of results earlier than the next, less selective columns.

Answer 3

Depending on the actual query you are sending, a composite index in two columns may be used, even if you are only looking for the second column. However, you will not get an index search, but most likely an index scan. If this is "good enough" for you, depends on your specific environment. Indexing is more an art than a science, and there are many factors that influence your decision about how to index a table. This is always a compromise, since too many indexes on the table are as bad as too few. Make sure your most important queries are well covered, and then decide on a case-by-case basis if any additional index is worth its price.

Also, since it has not been mentioned yet and provided to you, at least on SQL Server 2005: let me include the INCLUDE clause for non-clustered indexes. This is a missed but really useful addition to any indexing strategy.