Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Column Order in an Index

By Sarvesh Singh, (first published: 2010/11/23)

In this article I am going to explain that the order of the columns in an index is important.

Let's look at some examples. I am using the Person.Contact table from AdventureWorks. Create a non-clustered composite index as follows.


CREATE INDEX NonClust_Contact_Name
ON Person.Contact(LastName, FirstName);

A composite index is an index made up of more than one column. The main reason composite indexes are used is to make an index more selective.

Let's run the following query to find Contact named Catherine Abel.

select
 lastname
 ,firstname
 ,emailaddress
from
 Person.Contact
where lastname='Abel' and 
 firstname='Catherine'

If the non clustered index NonClust_Contact_Name wasn't there it would have searched the entire table to satisfy the query. With the non clustered index there it does an index seek as shown in Fig 1.

Fig 1

To retrieve the emailaddress a key lookup was used for each row. If you notice the WHERE clause in the select statement it appears in the same order as the 'index NonClust_Contact_Name'.

Let's now reverse the order of the WHERE clause in the select statement. If you now run the select statement shown below you will still see the same execution plan as shown in Fig 1.

select
 lastname
 ,firstname
 ,emailaddress
from Person.Contact
where firstname='Catherine' 
and lastname='Abel'

You are seeing the same execution plan because SQL server is still using the same index 'NonClust_Contact_Name'. But this is not always the case.

Consider the select statement below. I've removed the firstname from the WHERE clause.

select
 lastname
 ,firstname
 ,emailaddress
from
 Person.Contact
where lastname='Abel'

When you look at the execution plan, you will still see the same execution plan as before (Fig 1). This is because the lastname in the WHERE clause is still in the order of the index 'NonClust_Contact_Name'.

Let's now do the magic. Change the WHERE clause to firstname='Catherine'. Instead of lastname you are now searching for the firstname. The select statement is shown below.

select
 lastname
 ,firstname
 ,emailaddress
from
 Person.Contact
where firstname='Catherine'

The execution plan that you will get is shown in Fig 2.


Fig 2

If you notice SQL server is now using index scan operation to satisfy the query instead of the index seek as in fig 1. So why is this happening? Consider a telephone book. If you were to search for the firstname of Catherine, the telephone book wouldn't be very useful as you will have to start on the first page and search for every entry to see if the first name is Catherine. This is because telephone book is not sorted by firstname; it is sorted by lastname, firstname. Similarly in SQL Server, an index cannot be used to get rows of data if the first column from the index is not used in the WHERE clause.

This does not mean that the index 'NonClust_Contact_Name' was not useful. It's still much better than the Clustered scan. So if ths index did not exist, SQL Server would have done a clustered scan or a table scan which is a more expensive operation.

This article was to show you that the order of the column in an index does matter.

Total article views: 20459 | Views in the last 30 days: 9
 
Related Articles
FORUM

how to devied fullname into firstname and lastname

how to devied fullname into firstname and lastname

FORUM

Lastname, Firstname switch

I dont know if this is any help but this is the code I started with: SELECT HOST9006.DESCRIPTION,...

FORUM

Split out FirstName, LastName, MiddleName

One column name as [MemberName] in which stored data like LastName, FirstName M (with initial mi...

FORUM

Create FirstName and LastName to Replace Existing FirstName and LastName

Hello Everyone I hope that you all are having a very nice day. I am wanting to change all the Fi...

FORUM

PARSE FULL NAME INTO LASTNAME,FIRSTNAME, MI

I have name data to import into a table. This data is in the following format: DOE/JOHN F JONE...

Tags
indexing    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones