What Is the Leftmost-Prefix Principle?

The leftmost-prefix principle explains how SQL Server uses the column order of a composite index.

A composite index contains two or more key columns:

CREATE INDEX IX_Employees_Department_Name_Salary
ON Employees(DepartmentId, EmployeeName, Salary);

The index key order is:

DepartmentId → EmployeeName → Salary

SQL Server can efficiently seek through this index when the query starts with the leftmost indexed column and then follows the defined column order.

How the Index Is Organized

For the following index:

(DepartmentId, EmployeeName, Salary)

The values are logically ordered like this:

The rows are sorted:

1. First by DepartmentId

2. Within each department, by EmployeeName

3. For duplicate names within a department, by Salary

Therefore, SQL Server can directly navigate to a particular department.

However, it cannot directly navigate to every employee named Syed without knowing the department, because Syed values may appear in different parts of the index.

Prefixes Supported by the Index

For this composite index:

(DepartmentId, EmployeeName, Salary)

The useful leftmost prefixes are:

DepartmentId

DepartmentId, EmployeeName

DepartmentId, EmployeeName, Salary

The following are not leftmost prefixes:

EmployeeName

Salary

EmployeeName, Salary

DepartmentId, Salary

However, a query using DepartmentId and Salary can still use the index to seek by DepartmentId. It normally cannot seek efficiently through Salary after skipping EmployeeName.

Examples

1. Filter using the first column

SELECT *
FROM Employees
WHERE DepartmentId = 10;

This can efficiently use the index because DepartmentId is the first column.

Index Seek: DepartmentId = 10

2. Filter using the first two columns

SELECT *
FROM Employees
WHERE DepartmentId = 10
  AND EmployeeName = 'Syed';

This can efficiently use both columns because the query follows the index order:

DepartmentId → EmployeeName

3. Filter using all index columns

SELECT *
FROM Employees
WHERE DepartmentId = 10
  AND EmployeeName = 'Syed'
  AND Salary = 70000;

SQL Server can use the complete index key for an efficient seek.

4. Skip the first column

SELECT *
FROM Employees
WHERE EmployeeName = 'Syed';

This does not satisfy the leftmost-prefix principle because DepartmentId is missing.

SQL Server may:

• Scan the composite index

• Scan another index

• Use a different suitable index

• Scan the table

To support this query efficiently, create a separate index:

CREATE INDEX IX_Employees_EmployeeName
ON Employees(EmployeeName);

5. Use the first and third columns

SELECT *
FROM Employees
WHERE DepartmentId = 10
  AND Salary = 70000;

SQL Server can seek to DepartmentId = 10.

However, because EmployeeName is between DepartmentId and Salary, SQL Server generally cannot use Salary as the next direct seek key.

It may:

1. Seek to DepartmentId = 10.

2. Read the qualifying rows.

3. Apply Salary = 70000 as a residual predicate.

This index can still be useful, but not all its key columns are being used equally efficiently.

Equality and Range Conditions

The leftmost-prefix principle also interacts with range conditions.

Consider:

CREATE INDEX IX_Employees_Department_Salary_Name
ON Employees(DepartmentId, Salary, EmployeeName);

Equality conditions

WHERE DepartmentId = 10
  AND Salary = 70000
  AND EmployeeName = 'Syed';

SQL Server may use all three columns as seek predicates.

Range condition

WHERE DepartmentId = 10
  AND Salary > 50000
  AND EmployeeName = 'Syed';

SQL Server can generally seek efficiently using:

DepartmentId = 10
Salary > 50000

Once a range condition is used on Salary, the next column, EmployeeName, generally cannot further narrow the same contiguous seek range. It may be evaluated as a residual predicate.

A useful design guideline is:

Equality columns → Range columns → Other required columns

This is a guideline, not an absolute rule. Actual selectivity, sorting, joins and workload must also be considered.

Does the WHERE-Clause Order Matter?

No. The order of conditions in the WHERE clause generally does not matter.

These queries are logically equivalent:

SELECT *
FROM Employees
WHERE DepartmentId = 10
  AND EmployeeName = 'Syed';

SELECT *
FROM Employees
WHERE EmployeeName = 'Syed'
  AND DepartmentId = 10;

The Query Optimizer can rearrange predicates.

What matters is the order of columns in the index:

(DepartmentId, EmployeeName)

The physical order of conditions written in the query does not need to match the index definition.

Effect on ORDER BY

The leftmost-prefix principle also affects sorting.

For this index:

CREATE INDEX IX_Employees_Department_Name
ON Employees(DepartmentId, EmployeeName);

The following query may avoid a separate sort:

SELECT DepartmentId, EmployeeName
FROM Employees
ORDER BY DepartmentId, EmployeeName;

The requested order matches the index order.

This query may also use the index order effectively:

SELECT EmployeeName
FROM Employees
WHERE DepartmentId = 10
ORDER BY EmployeeName;

Because DepartmentId is fixed to one value, the remaining rows are already ordered by EmployeeName.

However, this may require an additional sort:

SELECT DepartmentId, EmployeeName
FROM Employees
ORDER BY EmployeeName;

The index is primarily ordered by DepartmentId, not globally by EmployeeName.

Effect on GROUP BY

An index can help grouping when the grouping columns follow its ordering.

SELECT DepartmentId, EmployeeName, COUNT(*)
FROM Employees
GROUP BY DepartmentId, EmployeeName;

An index on:

(DepartmentId, EmployeeName)

may help SQL Server process the grouped data more efficiently.

But grouping only by EmployeeName cannot fully benefit from the index ordering because DepartmentId is the leading key.

How to Choose the Column Order

Consider placing columns in a composite index according to:

1. Columns frequently used in equality filters:

DepartmentId = 10

2. Columns frequently used in joins:

Employees.DepartmentId = Departments.DepartmentId

3. Columns used in range conditions:

OrderDate >= @StartDate

4. Columns required for sorting or grouping:

ORDER BY OrderDate

5. Query selectivity and actual workload

Example query:

SELECT OrderId, TotalAmount
FROM Orders
WHERE CustomerId = @CustomerId
  AND OrderDate >= @StartDate
ORDER BY OrderDate;

A suitable index may be:

CREATE INDEX IX_Orders_Customer_OrderDate
ON Orders(CustomerId, OrderDate)
INCLUDE (OrderId, TotalAmount);

Here:

• CustomerId supports equality filtering.

• OrderDate supports the range and ordering.

• Selected columns are included to cover the query.

Common Misunderstandings

Misunderstanding 1: Every indexed column can be searched independently

For an index on:

(A, B, C)

A query on C alone does not normally get an efficient seek merely because C is part of the index.

Misunderstanding 2: WHERE-condition order must match the index

This is incorrect:

WHERE B = 20 AND A = 10

can still use an index on (A, B) because SQL Server’s optimizer analyzes the predicates independently of their written order.

Misunderstanding 3: A skipped column makes the entire index useless

For a query using A and C against an index on (A, B, C), SQL Server can still seek by A. It may then filter using C.

Therefore, the index may be partially useful.

Misunderstanding 4: The most selective column must always come first

Selectivity is important, but it is not the only consideration.

Column order should also consider:

• Equality predicates

• Range predicates

• Join conditions

• Sorting

• Grouping

• Query frequency

• Whether the index supports multiple important queries

Key Points

• The leftmost-prefix principle applies to composite indexes.

• SQL Server uses composite indexes most efficiently when queries start with the leading index column.

• An index on (A, B, C) supports the prefixes (A), (A, B) and (A, B, C).

• Searching only by B or C usually cannot use a direct, efficient seek.

• Searching by A and C may seek by A and filter by C.

• A range condition can limit how later key columns participate in an index seek.

• The order of conditions in the WHERE clause does not matter.

• The order of columns in the index definition matters greatly.

• Included columns do not participate in the leftmost-prefix rule because they are not index key columns.

• Confirm index behavior using the Actual Execution Plan.

Interview Answer

The leftmost-prefix principle means that a composite index is most efficiently used when a query filters using its leading column or a continuous sequence of columns starting from the left. For an index on (A, B, C), SQL Server can efficiently seek using (A), (A, B) or (A, B, C). A query using only B or C usually cannot perform an efficient seek because the leading column A is missing.