SQL Server Interview Question #126

What is a composite index?

Indexes & Query Performance Senior Advanced

Detailed Explanation

A composite index contains more than one key column. The order of the key columns matters because SQL Server can efficiently use the leading portion of the index for many seek and ordering patterns.

For an index on (CategoryId, Price), a query filtering CategoryId can often use it efficiently, and a query filtering both CategoryId and Price may also benefit. A query filtering only Price may not be able to use the same index as effectively for a seek because Price is not the leading key.

Code Example

CREATE INDEX IX_Products_CategoryId_Price
ON dbo.Products(CategoryId, Price);