4

I’m creating a table with a primary key clustered index. When I’m creating without asc/desc, what is the default sorting order? Which sorting order (ASC or DESC) is more efficient? Here is the sample code.

Create table employee (
  Name nvarchar(30),
  City nvarchar(30),
  Dob date,
  Constraint pk_employee primary key clustered
  (Name asc, dob asc)
)
Go
Madhukar
  • 1,194
  • 1
  • 13
  • 29
  • 1
    ASC is default. It depends what kind of seek are performed. ASC is more efficient when working with identity. –  May 09 '19 at 12:09
  • From the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017): "[ ASC | DESC ] Specifies the order in which the column or columns participating in table constraints are sorted. The default is ASC." Efficiency depends on how the index will be used. – HABO May 09 '19 at 12:21
  • @HABO what about the efficiency? – Madhukar May 09 '19 at 12:21
  • 1
    I have to ask about your primary key. Are you expecting to have multiple people with the same Employee_id but different birth dates? – Sean Lange May 09 '19 at 13:09
  • @SeanLange it is just an example. You can assume in that way – Madhukar May 09 '19 at 13:14
  • 2
    Well then I would say that your primary key indicates an issue in your design. As for the question of index efficiency based on order...it depends. If one or the other was always the better choice why would there be an option? Sounds like you need to do some deeper reading about how indexes work. [Here](https://www.sqlservercentral.com/stairways/stairway-to-sql-server-indexes) is an awesome series on indexes. Starts out basic and goes as deep as you want to dive. – Sean Lange May 09 '19 at 13:22
  • If you have duplicate `Employee_id` values in an `Employee` table, efficiency is the last thing you should care about. You have a serious bug that needs fixing – Panagiotis Kanavos May 09 '19 at 13:29
  • @PanagiotisKanavos as I said before, this is just an example code where you can replace employee_id with name or something else. – Madhukar May 09 '19 at 13:30
  • Corrected the sample code for better understanding. – Madhukar May 09 '19 at 13:32

4 Answers4

1

ASC is default. It depends what kind of seek are performed. ASC is more efficient when working with identity.

  • What happens to the efficiency if the index is built using multiple columns? Which sorting order is better in that case? – Madhukar May 09 '19 at 12:17
  • You should make an experiment for your table Employee. In general, as I wrote above, ASC is more efficient when working with identities. –  May 09 '19 at 12:25
1

Indexes are stored as binary trees, so I would say that there won't be any difference in performance.

In both cases, ASC and DESC, SQL needs to go down to get the leaf (record included in resulset).

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • 2
    Unfortunately, there is a difference. When you look through the index in the opposite direction, SQL Server can't parallelise the scan if it needs to. For example, see https://blogs.msdn.microsoft.com/naga/2015/04/19/sql-server-performance-tuning-backward-scanning-of-an-index/ – Roger Wolf May 09 '19 at 12:34
  • 2
    It also makes a difference to how logically fragmented your index is https://stackoverflow.com/a/9382500/73226 – Martin Smith May 09 '19 at 12:39
  • Actually, they are stored in a **B+tree** and they have the following feature: _… they contain a pointer for each row that identifies the next child page._, see [B+Trees – How SQL Server Indexes are Stored on Disk](https://sqlity.net/en/2445/b-plus-tree/). So they do not have a pointer to the previous page, which explains the different performances. However, I assume that if you do an `order by Id desc` on a log table, with Id an auto-increment field, you would actually benefit from an index on Id in descending order. – R. Schreurs Feb 04 '22 at 19:45
  • Hm, after reading the comment by @RogerWolf and the answer he is referencing, I conclude that it is not wise to put a descending order index on an auto-increment Id, because of the bad impact on inserts, whatever the effects on retrieval. – R. Schreurs Feb 04 '22 at 20:04
1

Ascending order is more efficient. This is true with all RDBMS I have worked with and it's true for a number of reasons. The biggest issue with descending sorts (ordered-backward scans) in SQL Server is that ordered-backward scans cannot leverage a parallel execution plan.

Keep in mind that sorting has a n(log n) complexity which is slower than linear. In other words, each row gets more expensive to sort as you add more rows. This is why the optimizer often chooses a parallel execution plan to handle sorts. If you have a lot of rows to sort you want your optimizer to have the option to parallelize the sort. So, again - ascending is more efficient.

There are other optimizations not available to the optimizer when you do an ordered-backward scan. For example, when using partitioned window functions (function that use the OVER clause and PARTITION BY) ascending is usually more efficient.

Here are two really good articles about this topic (both by Itzik Ben-Gan):

SQL Server: Avoiding a Sort with Descending Order

Descending Indexes

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
  • 2
    ordered-backward scans just mean the scan isn't parallelized - it doesn't prevent parallelism in the whole plan https://www.sql.kiwi/2011/12/forcing-a-parallel-query-execution-plan.html – Martin Smith May 09 '19 at 13:30
  • Thanks Martin. Yes, yes - I should have been more clear about that. – Alan Burstein May 09 '19 at 13:32
0

Ascending order is more efficient in clustered index. This is true with all RDBMS I have worked with and it's true for a number of reasons. The biggest issue with descending sorts (ordered-backward scans) in SQL Server is that ordered-backward scans cannot leverage a parallel execution plan.

ABDUL HAMEED ARABI
  • 9
  • 4