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.