2012年4月30日 下午 05:52
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?
2012年4月30日 下午 06:07
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).
My blog: http://aboutsqlserver.com
2012年4月30日 下午 06:09
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.
Index Sort Order
2012年4月30日 下午 06:24
Refer to this forum post