Primary key Ascending vs Descending

Question

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?

Answer 1

It makes absolutely no difference.

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

Answer 2

It also 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

Answer 3

If the index is clustered, you might cause significant performance problems when specifying the primary key as DESC. If DESC, the physical data rows would be in descending order. So when a new row is inserted into the database, the new identity would be considered the highest ranking number and would need to be stored at the beginning of the data pages. All other data would need to shift down by own. I imagine that this would cause significant page splits, etc.

So, if the PRIMARY KEY is CLUSTERED and IDENTITY (i.e. incrementing), then always use ASC. And as others have said, it doesn't matter when searching the TOP N ASC or DESC. SQL Server is smart enough to do it efficiently.