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.

CREATE NONCLUSTERED INDEX [IX_Itemtype] ON [dbo].[Items]
(
[ItemType] ASC
)
CREATE NONCLUSTERED INDEX [IX_Dates] ON [dbo].[Items]
(
[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.