34

In Sql Server, I have a table with an Identity primary key. Often I want the latest few new records, so I grab the Top n sorted by descending the primary key. Should I define the Primary Key index as Descending, or does it make no difference? i.e. if they are in Ascending order, then can sql just as efficiently work backwards?

Rex M
  • 142,167
  • 33
  • 283
  • 313
peterorum
  • 1,401
  • 2
  • 15
  • 21
  • No you should **not** create it as `desc`. You will end up with lots of logical fragmentation from inserts [as shown here](http://stackoverflow.com/a/9382500/73226) – Martin Smith Feb 21 '12 at 17:51

2 Answers2

46

From a purely querying standpoint, it makes no difference whether your key is descending or ascending if you want to pull N most recent or N oldest records:

The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index, regardless of the sequence in which the keys are stored. For example, specifying DESC on a single-column index does not make queries with an ORDER BY IndexKeyCol DESC clause run faster than if ASC was specified for the index.

http://msdn.microsoft.com/en-us/library/aa933132(SQL.80).aspx

However under just about any normal circumstance, you want your primary key to be ascending and ordinally sequential to prevent fragmentation. SQL Server is optimized for physically appending new records to the end of the database file. If it needs to insert each new record at the top and push everything down, it would probably result in nearly 100% fragmentation.

Rex M
  • 142,167
  • 33
  • 283
  • 313
  • 2
    "The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index" - what about for multi-column indexes? (Also, the MSDN link is broken now) – Dai May 14 '22 at 17:23
3

It makes absolutely no difference.

I can't even imagine why it's possible to declare it either way.

dkretz
  • 37,399
  • 13
  • 80
  • 138
  • 6
    With a single column index, it makes no difference, but ASC and DESC come into play when you have multiple columns in the index, and the order matters for the second/third/fourth/etc column. – Jim McLeod Mar 16 '09 at 06:24
  • This makews no sense to me. If you have two field values, how you descend the tree is completely indifferent. Is that just a guess, or do you have a reference? – dkretz Mar 16 '09 at 12:24
  • 4
    Makes sense to me. If you have an index declared `A ASC, B DESC` but most of your queries are `ORDER BY A ASC, B ASC` that isn't going to avoid a sort. – Martin Smith Dec 26 '11 at 19:46