When I talk about indexes, people always ask me if the sorting order of the columns in a composite Non-Clustered Index matters? The short answer is always “It depends”. Let’s have a look on it why “it depends”..

Singleton Lookups

When you have queries which perform Singleton Lookups on your tables, the sorting order of the columns in a composite Non-Clustered Index doesn’t really matter. Imagine the following query:

-- Without a supporting Non-Clustered Index we have to scan the complete Clustered Index
SELECT AddressID FROM Person.Address
WHERE StateProvinceID = 79 AND City = 'Bothell'
GO

You can now create a Non-Clustered Index on StateProvinceID and City, or on City and StateProvinceID:

-- Create a supporting Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Test ON Person.Address(StateProvinceID, City)
GO

-- SQL Server performs a Non-Clustered Index Seek operation in combination with a Seek Predicate
SELECT AddressID FROM Person.Address
WHERE StateProvinceID = 79 AND City = 'Bothell'
GO

-- Change the column ordering
CREATE NONCLUSTERED INDEX idx_Test ON Person.Address(City, StateProvinceID)
WITH (DROP_EXISTING = ON)
GO

-- The column ordering doesn't matter in the Non-Clustered Index
SELECT AddressID FROM Person.Address
WHERE StateProvinceID = 79 AND City = 'Bothell'
GO

The ordering of the columns in the Non-Clustered Index doesn’t really matter here, because SQL Server will just perform a simple Non-Clustered Index Seek operation (in combination with a Seek Predicate) in the execution plan:

Range Scans

Things change when we talk about Range Scans on tables, where you retrieve a set of data. Imagine you execute the following query:

SELECT AddressID FROM Person.Address
WHERE StateProvinceID BETWEEN 10 AND 12 AND City = 'Bothell'
GO

Again, you have 2 choices for a supporting Non-Clustered Index:

• Non-Clustered Index on StateProvinceID and City
• Non-Clustered Index on City and StateProvinceID

Let’s create the first choice:

-- Create a supporting Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Test ON Person.Address(StateProvinceID, City)
GO

In that case, SQL Server performs for the query a Non-Clustered Index Seek operation on the column StateProvinceID, and has to evaluate a Residual Predicate for the column City as you can see in the generated execution plan:

This is not really a perfect execution plan, because you are reading physically more data than you have logically requested. But with the sorting order based on StateProvinceID as a leading column and City as a subsequent column this is the only possible behavior as you can see from the following picture:

But let’s try now to create the Non-Clustered Index where we just swap the columns: the City column as a leading column, and the column StateProvinceID as the second one:

-- Non-Clustered Index Seek on StateProvinceID, and a Residual Predicate on column City
SELECT AddressID FROM Person.Address
WHERE StateProvinceID BETWEEN 10 AND 12 AND City = 'Bothell'
GO

When you now execute your query again, you can see that SQL Server again performs a Non-Clustered Index Seek operation. But this time you have *no* Residual Predicate for your query.

Therefore you are physically reading just those rows that you logically requested. But how is this now possible? Just have a look at the following picture that shows you how the data is currently sorted in the Non-Clustered Index:

As you can see the data is now presorted on City, and within each City group you have presorting on the column StateProvinceID. Therefore you can get straight to those records that you logically requested – and return them without any further evaluating of a Residual Predicate!

Summary

The column ordering in a composite Non-Clustered Index matters – when you have to perform Range Scans! As I’m always saying in my various workshops: almost everything in SQL Server is about Indexing, and Indexing itself is just presorting data! Nothing more! To understand whether SQL Server can directly seek to logically requested records, you also have to visualize in your mind how the data is presorted, and how you can access it by presorting the data efficiently.

I hope that this blog posting gave you a better understanding of how the column ordering in a Non-Clustered Index influences a Seek operation.

Thanks for your time,

-Klaus