Non Clustered Index [Date Column Desc]

Question

All, We have a Huge table consisting 1 Bn Records, its a Table on which alot of Analytical Processing is done, Users analyze Data.

The way the USERS mostly access/ want to access data is from Current Date and Go back.

Example: Sales for last 6 Months (Going from Today --> back 6 months)

So you see the way they analyze is in DESCENDING Order

So my Question:

DOES it HELP to have NON CLUSTERED DESC, or it makes no difference?

Answer 1

SQL Server can scan index in both directions (pages are linked to double-linked list). So in case, if you have the index on 1 column, sorting order would not make a lot of difference. In addition to that, if you are using set-based queries, the order how SQL Server selects the data could be different from your processing order.

And of course, that's another question if SQL Server decides to use non clustered index for your query. In case if index is not covering the query (e.g. data from clustered index - key lookup operation - is requires), SQL Server would not use non-clustered index if it expects that it would select more than very small percentage of the rows from the table (threshold varies based on a few factors but it's very small). 

use tempdb
go

create table #T(ID int not null)
go

;with CTE(ID)
as
(
	select 1
	union all 
	select ID + 1 from CTE where ID < 100000
)
insert into #T(ID)
	select ID from CTE
option(maxrecursion 0)
go

create unique index IDX1 on #T(ID)
go

set statistics io on
select SUM(t.ID)
from
(
	select top 25000 ID 
	from #T 
	where ID < 50000 
	order by ID DESC
) t
set statistics io off
go

drop index IDX1 on #T
go
create unique index IDX1 on #T(ID DESC)
go

set statistics io on
select SUM(t.ID)
from
(
	select top 25000 ID 
	from #T 
	where ID < 50000 
	order by ID DESC
) t
set statistics io off
go

PS. I think Chirag and I are talking about different things. If we talk about performance difference between index in ASC order vs. index in DESC order - for ordered scan it would not make a lot of difference. 

Same time if we are talking about NO INDEX vs. INDEX situation - yes, in that case INDEX could help your queries assuming SQL Server decides to use the index. It's impossible to say if index would be used without knowledge of your data and queries. But as I mentioned above - general rule - index would not be used in case if you need to select more than X% of the rows from the table (and X is very low). 

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

IMHO,

If most of your queries using ORDER BY with latest date first, yes it would make sense to create index with DATE DESC.

Index is double linked list that can be traverse on either side; however by creating index with DESC you may eliminate expensive Sort operation in exeuction plan. As alwasy test before you implement.

Reference:

Index Sort Order

http://msdn.microsoft.com/en-us/library/ms181154.aspx

Answer 3

In addition,

Refer to this forum post

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f90e2cdb-6c1b-40fa-941d-1c3b4d6225de