Thursday, January 7, 2010

Indexes and Order by clause

Have you ever thought about “order by” clause that any of your query is using in your application when designing index for table? If not you may start checking it after reading this post. You will also know if there is any relationship between index and order by clause.

If you are using any index with single column and using order by in your query it doesn’t make any difference. I have created a sample table called Items just to explain different scenarios.

Here is my table

CREATE TABLE [dbo].[Items](
[ItemiD] [int] NOT NULL,
[CreatedDate] [datetime] NULL,
[ClosedTime] [datetime] NULL,
[ItemType] [int] NULL,
[Status] [char](1) NULL

Add few records just get something when we do the query.

Lets add below indexes.

[ItemType] ASC
[CreatedDate] ASC,
[ClosedTime] ASC

I have two indexes created now. One with single column and another with two columns. Let me go through some cases with different combinations.

Before executing below queries, enable Executaion plan window. You can do this by Right click on Query panel and select Include Actual Exection plan.

Query: Select * from Items order by Itemtype

Result Execution plan

Query 2: Select * from Items order by Itemtype desc

If you look at the Estimated cost for above queries, both the queries has the same value and ASC and DESC has no effect on performance.

Now let us check the same with multiple columns.

Query 3: Select * from Items order by createddate, closedtime

The cost still the same.
Query 4: Select * from Items order by createddate desc, closedtime desc

Even with this query, everything looks ok and no effect on performance. Now let us look at the last option

Query 5: Select * from Items order by createddate, closedtime desc

Here you can find a major difference with the cost and also if you observe the execution plan, you can notice that the Index Scan step is missing. The indexes are created with asc order for both the coumns. When the query uses both the coulumns with different orders, index will not be used. You need to be careful when you are defining indexes for multiple columns especially when those columns are used on order by clauses. This may degrade the system. Most of the time we will be in impression that the indexes are created for making the search faster and do not have to worry on Order clause. Indexes are also important for order by clause and we should consider different possibilities before creating multiple column indexes.


  1. One question Subbu. Is there any particular reason for using Non-Clustered index in your example?
    Also, the common known fact (I am not sure if I am correct here, please correct me if I am wrong) is that try avoiding multiple indexes on a single table because it potentially decreases the performace.

  2. jagadish - There is no particular reason taking for non clustered but most of the time we get this situation when using non clustered index only.
    We use multiple indexes in many scenarios. I dont think it will decrease the performance. If you have a query which requires multiple indexes then definitely you can go for it. I will try to explain some situations when we need to go for multiple indexes.