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.