1

I have a screen where i show all the latest posts and when the user click the read more link, i bring the next set based on Post Id(where id < latest one already shown).

I have a clustered index ascending on field Id on Posts table.

I was wondering if i am always showing the latest posts first, isn't it better to create the clustered index descending ?

Thanks

user636525
  • 3,180
  • 9
  • 38
  • 51
  • Try performance testing it – Brandon Dec 14 '13 at 16:41
  • 1
    If you create it descending [it will likely end up heavily fragmented](http://stackoverflow.com/questions/743858/sql-server-indexes-ascending-or-descending-what-difference-does-it-make/9382500#9382500) – Martin Smith Dec 14 '13 at 19:10

1 Answers1

2

It will not matter in this case. SQL Server can use an ascending index and scan it backwards. The scan direction is visible in the properties of the Clustered Index Seek operator.

Sample data:

create table T
(
  ID int identity,
  constraint PK_T_ID primary key clustered (ID asc)
)

go

insert into T default values

go 10

Query:

select top(3) *
from T
where ID < 7
order by ID desc

Query plan:

enter image description here

Select the Clustered Index Scan operator and press F4 to view properties.

enter image description here

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • I'm wondering why you have the option to specify direction when you're creating the index? What is this help at? – bjnr Dec 14 '13 at 20:42
  • 1
    @SQLhint.com - Backward scans can't be parallelised and aren't always considered for some window function queries where they could actually be used. The main use though is in composite indexes. If you want to use an index to avoid a sort for the query `ORDER BY A DESC, B ASC` then an index on `A ASC, B ASC` won't help. – Martin Smith Dec 15 '13 at 00:52
  • For querying, descending or ascending makes no difference. BUT for inserts, a descending clustered index on an incrementing PK will cause constant reorg of the first page. There's a good answer to a similar question here: https://stackoverflow.com/a/649319/441729 – grahamesd Sep 07 '18 at 15:30