Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

Does the order of columns in an index matter?

A single column index is fairly straightforward to understand. You may have heard it compared to the index in the back of a technical book. When you want to find some information in the book, say DBCC INPUTBUFFER, you turn to the index in the back of the book and look up DBCC INPUTBUFFER. The index doesn't actually contain the information on DBCC INPUTBUFFER, it has a pointer to the page in the book where the command is described. So, what do you do? You turn to that page and read all about how DBCC INPUTBUFFER may be used. This is a good analogy for a single column non-clustered index.

Composite Indexes

In Microsoft SQL Server, you can also create an index that contains more than one column. These are known as composite indexes. A good analogy for a composite index is the telephone book.

A telephone book lists every individual in the area who has a publicly available telephone number. It's organized not by one column, but two: last name, first name. (Ignoring the middle initial that is sometimes listed but most often treated as an extension of the person's first name). If you wanted to look up someone in the telephone book, you'd first navigate to the last name and then the first name. For example to find Jake Smith, you'd first locate the Smiths, then within the Smiths, you'd find Jake. The same holds true for a composite SQL Server index.

This is all good to know, but how does it really affect query resolution?

Well, let's consider an example. Let's assume you have a Customers table as described below.

CREATE TABLE Customers
(
Customer_ID INT NOT NULL
IDENTITY(1,1)
,Last_Name VARCHAR(20) NOT NULL
,First_Name VARCHAR(20) NOT NULL
,Email_Address VARCHAR(50) NULL
)

It has a clustered index on Customer_ID and composite index on the Last_Name, First_Name columns as shown below.

CREATE CLUSTERED INDEX ix_Customer_ID 
ON Customers(Customer_ID)
CREATE INDEX ix_Customer_Name 
ON Customers(Last_Name, First_Name)
 

Finding a specific row

To find a specific row, we could execute the following query.

SELECT
*
FROM
Customers
WHERE
Last_Name = 'smith' AND
First_Name = 'Jake'

It should be pretty obvious that the ix_Customer_Name index would work well to satisfy this query. A quick look at the execution plan confirms our expectations.

CompositeIndexes0-2008-02-13

Finding a last name

Now, let's broaden our search a bit to retrieve all customers whose last name is Smith. The following query may be executed for this.

 
SELECT
*
FROM
Customers
WHERE
Last_Name = 'smith'

Looking at the query execution plan, we can see that SQL Server did indeed use the ix_Customer_Name composite index; it performed an index seek to find the rows that satisfied the query, then it used a Key Lookup to retrieve the non-indexed column information. You'll notice that this time, however, more work was expended in the Key Lookup than in the Index Seek.

CompositeIndexes1-2008-02-13

Returning to our telephone book analogy, we can see why this index was deemed efficient by the Query Optimizer. To find all of the Smiths in the telephone book, we'd navigate to the page that contains the first Smith and keep moving forward until we found something other than Smith.

Finding a first name

Now, let's see what happens if we need to find all people who have a first name of Jake. Let's execute the following query.

SELECT
*
FROM
Customers
WHERE
First_Name = 'Jake'

This yields the following query execution plan.

CompositeIndexes2-2008-02-13

Notice that this time, SQL Server used a Clustered Index Scan to resolve the query. This is tantamount to a complete table scan. It did not use our ix_Customer_Name index.

Once again, returning to the telephone book example, we can see why. Think about find all of the Jakes in the telephone book. You'd have to start on the first page of the book and look through every entry. Why? Because it's not organized by first name; it's organized by last name, first name.

Does this mean that composite indexes are worthless? No. They have great value to SQL Server. In our first example we were able to use the ix_Customer_Name index to navigate directly to Smith, Jake. We just need to give considerable forethought to the kinds of queries our applications will be submitting to our server and create the appropriate indexes to handle those queries.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Wednesday, February 13, 2008 10:43 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: Does the order of columns in an index matter?

Great intro - I'm passing this along to some new DBA's. What are you using to make the screen shots with the "torn" look?

Thanks!

- Buck
2/14/2008 9:25 AM | Buck Woody
Gravatar

# re: Does the order of columns in an index matter?

Thanks.I'm glad you found it useful.

I've become a big fan of the TechSmith products, http://www.techsmith.com/. I used SnagIt to create and edit the images, including the torn page affect. About 3 mouse clicks and it's done.

I also like their Camtasia product for recording audio/video presentations.

Thanks for the comments!

Joe
2/14/2008 9:35 AM | Joe Webb
Gravatar

# re: Does the order of columns in an index matter?

Nice intro! I'm curious to know if changing the order of the criteria in the WHERE clause makes a difference. In other words:

SELECT *
FROM Customers
WHERE first_name = 'Jake'
AND last_name = 'Smith'

Would the composite index still be used as in the first example you showed?
2/21/2008 2:32 PM | Carlos A
Gravatar

# re: Does the order of columns in an index matter?

Generally speaking the order of items in the WHERE clause makes no difference in the query plan or performance of the query. The optimizer evaluates and optimizes this for you. I'll expand on this idea in another post.

Thanks!

Joe
2/22/2008 9:47 AM | Joe Webb
Gravatar

# re: Does the order of columns in an index matter?

nice post with real cool real world example (telephone book)... could not get simpler than this :-)... i love your posts mate...
2/24/2008 3:59 AM | Raj
Gravatar

# re: Does the order of columns in an index matter?

Very clear!

I think the order of itens in WHERE makes difference only in old versions of Sql Server (sybase times?).
2/25/2008 10:29 AM | Carlos E. A. Klein - DigitaDesk
Gravatar

# re: Does the order of columns in an index matter?

Thanks! I'm glad you've found it useful.

I didn't start working with SQL Server until 6.0. And it wasn't until 6.5 that I really got serious with it. But your timeline sounds about right to me.

Cheers!

Joe
2/25/2008 10:36 AM | Joe Webb
Gravatar

# re: Does the order of columns in an index matter?

Thank you, that is the clearest explanation I've ever read.
3/3/2008 4:39 PM | Mark
Gravatar

# re: Does the order of columns in an index matter?

didn't realise it made such a massive difference - i've got a 6 second query down to 1.4 seconds just by swapping two columns around in the PK index :D

now to get those last few milliseconds off...
3/27/2008 11:37 AM | anonymous
Gravatar

# re: Does the order of columns in an index matter?

Thanks for the clear explanation. I'm curious though, wouldn't it be safer than to just create two seperate indexes, one for Last Name and one for First Name? What's the advantage of the composite in this situation?
7/21/2009 2:12 PM | Chris
Gravatar

# re: Does the order of columns in an index matter?

how many rows did u insert in the table.? in the second example ideally we should have index scan if the number of rows are less compared to total row count.

4/7/2010 12:38 PM | naval arya
Gravatar

# re: Does the order of columns in an index matter?

another great post, thank you very much!
6/16/2010 12:20 AM | joueur
Gravatar

# re: Does the order of columns in an index matter?

Thanks, Joueur! I'm glad you found it useful.

By the way, my new blog is located at http://www.webbtechsolutions.com/blog.

Joe
6/16/2010 6:07 AM | Joe Webb
Gravatar

# re: Does the order of columns in an index matter?

Joe, risking posting a comment here because you apparently checked it last month, and I can't get to your new blog at the moment...

How does this have anything to do with the ORDER of the columns in the index? I can see how the one plan uses the composite index, the other can't because there isn't an index on just the firstname, but that's not the order, that's just whether the index exists or not?

Or am I missing something (more than slightly possible)?

Thanks,

Jon
7/22/2010 12:21 PM | jonmcrawford
Gravatar

# re: Does the order of columns in an index matter?

you'd first locate the Smiths, then within the Smiths, you'd find Jake. The same holds true for a composite SQL Server index.
10/16/2010 3:53 PM | columbia jackets
Gravatar

# re: Does the order of columns in an index matter?

Now, let's see what happens if we need to find all people who have a first name of Jake. Let's execute the following query.
10/19/2010 4:17 AM | snow boots for women
Gravatar

# re: Does the order of columns in an index matter?

This is a good analogy for a single column non-clustered index.

womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets
10/23/2010 10:03 AM | the north face outlet
Gravatar

# re: Does the order of columns in an index matter?

You never seemed to address your title: "does the order of columns in an index matter". You talked about single and composite indexes, but never the "order of columns".

I'm trying to figure this one out for sure. I believe that it is true. I was just looking at a query which was running against some thousands of records which was taking about 1000ms to run every time. I considered the order of the indexes and that the index search could be broken down with some important boolean fields first.

My aim being, to narrow down the number of records most quickly, so the index searches are smaller. For example, consider these fields in an index:

Senario1.....
Field: number of matches against a static query (in a single index scenario)
field5: 50,000
field4: 25,000
field3: 5,000
field2: 200
field1: 100

... Poor index performance.

Scenario2....
field1: 100
field2: 200
field3: 5,000
field4: 25,000
field5: 50,000

... Good index performance - Field1 narrows down the set quickly to 100, from which field 2 will no longer select 200, rather it would approx select (200 / total rows)*100. Let's pretend



10/24/2010 7:11 PM | Todd
Gravatar

# re: Does the order of columns in an index matter?

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free video converter for Mac, This video converter for Mac is more stable in converting video files and support more video formats.(even HD videos, such TR, TP, TRP, M2TS). The only shortback of this Video converter for
10/26/2010 1:25 AM | hanly
Gravatar

# re: Does the order of columns in an index matter?

In our first example we were able to use the ix_Customer_Name index to navigate directly to Smith, Jake. We just need to give considerable forethought to the kinds of queries our applications will be submitting to our server and create the appropriate indexes to handle those queries.

MBT shoes clearance| snow boots for women | columbia jackets | mac makeup | the north face outlet
10/26/2010 1:49 PM | furry boots
Gravatar

# re: Does the order of columns in an index matter?

Professional Wholesale Designer Clothing Stores Where you can Buy High fashion But cheap clothes online in Very Low Price Including clothing accessories,Designer Mens womens clohing, handbags, Jewelry, Sunglass, fashion accessories and become a wholesaler now!
5/26/2011 10:56 PM | Cheap clothes
Gravatar

# re: Does the order of columns in an index matter?

A good example. But i think even in later versions of SQL Server like 2008, the order of columns does matter. I will explain it later.
6/2/2011 1:38 AM | Toqeer
Gravatar

# re: Does the order of columns in an index matter?

Joe Great Job :) i think i should focus on your point which is order of columns in an index matters. For the ones who want more examples on it, please follow the link after going through the simplist example provided by of Joe.

decipherinfosys.wordpress.com/...
6/2/2011 2:09 AM | Toqeer
Gravatar

# re: Does the order of columns in an index matter?

I don't understand this at all. Can you please be more clear with the examples with some screen shots of the work? It looks like it is made up to me, regardless what everybody is saying on this blog.
4/2/2012 6:56 AM | vic
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET