5

If a PK of a table is a standard auto-increment int (Id) and the retrieved and updated records are almost always the ones closer to the max Id will it make any difference performance-wise whether the PK clustered index is sorted as ascending or descending?

When such PK is created, SSMS by default sets the sort order of the index as ascending and since the rows most accessed are always the ones closer to the current max Id, I'm wondering if changing the sorting to descending would speed up the retrieval since the records will be sorted top-down instead of bottom-up and the records close to the top are accessed most frequently.

Dean Kuga
  • 11,878
  • 8
  • 54
  • 108
  • Doesn't seem like it would matter, the index in the Btree tells SQL what pages to get the data from. At which point, I don't think the direction of the sort would have any relevance. – Andrew Oct 10 '14 at 18:14
  • To add on to the b-tree responses, the number of pages touched will be the same `ASC` or `DESC`. `DESC` is generally beneficial when the specification matches a query `ORDER BY` clause. – Dan Guzman Oct 10 '14 at 18:22
  • 1
    Related to [this](http://stackoverflow.com/questions/743858/sql-server-indexes-ascending-or-descending-what-difference-does-it-make) which has a couple of pretty good answers. – Joachim Isaksson Oct 10 '14 at 18:29

2 Answers2

2

Indexes use a B-tree structure, so No. But if you have an index that is based off multiple columns, you want the most distinct columns on the outer level, and least distinct on the inner levels. For example, if you had 2 columns (gender and age), you would want age on the outer and gender on the inner, because there are only 2 possible genders, whereas there are many more ages. This will impact performance.

Dean Kuga
  • 11,878
  • 8
  • 54
  • 108
John Smith
  • 7,243
  • 6
  • 49
  • 61
  • 1
    This says the opposite: http://dba.stackexchange.com/questions/33196/multicolumn-index-and-performance . It recommends indexing Gender first (equality test) then Age (range test) – Anon Oct 10 '14 at 18:46
  • @Anon actually the top answer claims it "doesn't matter". But I can tell you from my own experience, it definitely does. You want the most selective on the outside. I had a query not too long ago, I had the indexes backwards on a temp table, and by doing this I cut down the speed from ~3 minutes to <2 minutes. – John Smith Oct 10 '14 at 19:17
  • @user3738391 read it more carefully: it says it doesn't matter *if both predicates are equality tests, not range tests*. In your case {age, gender}, an age range query is far more likely than a gender range query. Your 'more-selective first' idea is a good rule of thumb, but you picked a bad example with gender, because it will never be used as `SELECT * WHERE Gender BETWEEN 'M' AND 'T'` It's always either an equality test or not a predicate at all, which means it should be the outer index. – Anon Oct 10 '14 at 21:31
  • @anon wrong again. in that section (the 'answer' section) they were talking about a 2 column index that tested 1 column for equality and 1 column as a range, concluding that you index for equality first, then range. BUT THIS IS NOT BASED ON SELECTIVITY. they get into that a little lower, where he says "Apart from that, what if you have only equality conditions for both columns?". And he CLAIMS it "doesn't matter". this article gives no clear answer! but I can tell you from personal experience, it does make a difference. I have tested it on my own query. – John Smith Oct 11 '14 at 03:14
  • Adding your query and an explanation would improve your answer. Currently it contradicts other higher-rated SO answers on the same topic. – Anon Oct 14 '14 at 16:01
2

I don't think there will be any performance hit. Since, it's going to perform a binary search for the index key to access and then the specific data block with that key. Either way, that binary search will hit O(log N) complexity. So in total O(log N) + 1 and since it's clustered index, it actually should be O(log N) time complexity; since the table records are physically ordered instead of having a separate index page/block.

Rahul
  • 76,197
  • 13
  • 71
  • 125