Composite Index in SQL Server

Create a Composite Index

Given a table Orders with columns CustomerID, OrderDate, and Status, create a composite index to optimize queries filtering by CustomerID and OrderDate.

Expected output: A composite index on (CustomerID, OrderDate) created successfully.

Hint: Use CREATE INDEX with CustomerID as the first column, followed by OrderDate.

A composite index is an index that includes multiple columns from a table to optimize queries filtering or sorting on those columns together.

Because SQL Server uses the index starting from the first column, queries filtering on leading columns can use the index efficiently, while filtering only on later columns may not benefit.

Use a composite index when your queries commonly filter or sort on multiple columns together, improving performance by reducing the need for multiple single-column indexes.