What is the difference between ASC or DESC in clustered indexes?

Question

What is the difference between ASC or DESC in clustered indexes?

Navigation

#1 by (6 votes)

7

When I am going to create a non clustered index in SQL SERVER, SSMS appears in the option to choose whether the option "Columns" is ASC or DESC , as shown below: / p>

Generally, I use ASC or DESC to sort the results of the table, but in the case of the clustered index I do not understand the meaning very well.

I would like to know the following:

What is the difference between ASC and DESC in the case of defining a clustered index ?
Set ASC or DESC entails some noticeable performance difference or something else?
When should I use both? In this case, can I create a clustered index ASC and another DESC for the same column?

sql-server

asked by anonymous 30.08.2018 / 19:23

1 answer

How to put the regression equation on a graph? Is it possible to use Input Value as a CSS selector?

score 6 · Accepted Answer

Suck on an answer in the SO :

If you often do a lot of searching backwards (and this is not common) it may be faster if you use DESC in the SQL Server edition that supports parallel searches, since only sequential forward lookups can be parallelized. So if you have ASC and have query DESC , it has no parallelization (today).

They also talk about index fragmentation. In fact it may be more complicated to do append in the index in reverse order. One of the reasons for using the clustered index is to have the facility to add it in a natural way.

So I would avoid% clustered if you will do much append and if you do not use it almost exclusively to read backwards.

I do not really like database engines because they end up forcing you certain things. I'd rather use them just as storage and let it do what I want, which can give more flexibility on how to handle it.