Will SQL Server use a compound index when only a single column is in the WHERE clause?

Question

Say I've got a table:

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

Queries are usually on FirstName or LastName, but also on FirstName and LastName.

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

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

Does SQL Server store compound index parts left-to-right or right-to-left? In other words: will it build the key as LastNameFirstName or FirstNameLastName? Or is it free to choose one arbitrarily?

Answer 1

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

Yes, the database will use the index (LastName, FirstName) for queries on LastName. It will not use this index for queries only on FirstName though.

Does it store compound index parts left-to-right or right-to-left?

Storage is in a B-Tree. Whether you think of it as being stored right-to-left or left-to-right is just a useful visualization aid, and not related to the actual data storage.

Answer 2

Yes, if you query on LastName alone it should use the (LastName, FirstName) index. So it would be used both when querying by LastName alone, or LastName and FirstName together.

General guideline is to ensure the column with the greatest selectivity appears first in the compound index as this provides the most benefit/narrows the resultset down sooner before the following, less selective columns.

Answer 3

Depending on the actual query you're sending, a composite index on two columns may be used even if you search for the 2nd column only. However, you won't get an index seek, but most likely an index scan. If this is "good enough" for you, depends on your specific environment. Indexing is more of a art than a science and a lot of different factors influence your decision on how to index a table. It's always a trade-off as having too many indices on a table is just as bad as having too few. Make sure that your most crucial queries are covered well and then decide on a case by case basis whether any additional index is worth its cost.

Also, as it hasn't been mentioned yet and provided you're at least on SQL Server 2005: Let me throw in the INCLUDE clause for nonclustered indices. It is an overlooked, but really useful addition to any indexing strategy.