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

Asked

Viewed 4,263 times

8

When I’m going to create an index no clustered in SQL SERVER, appears in SSMS the option to choose if the option "Columns" is ASC or DESC, as shown below:

Indice clusterizado com valor ASC

It is usually used ASC or DESC for sorting the table results, but in the case of the index clustered I don’t quite understand the meaning.

I’d like to know the following:

  • What’s the difference between ASC and DESC in the case of the definition of an index clustered?
  • Define ASC or DESC results in some noticeable difference in performance or other?
  • When should I use one and the other? In case, I can create an index clustered ASC and another DESC for the same column?
sql-server índices
  • 2

    I believe that to start with the normal table (asc) or "upside-down" (desc) depending on whether to have a standard query for example, which are usually in the first records, or last... (accompanying) +1

  • @Rbz means that if I put ORDER BY coluna_com_o_indice_clusterizado_com_DESC then the results will be DESC, even though I haven’t defined it in ORDER BY?

  • No. For example: You have the "never stop" of the toll. It has a code referring to your sign. Imagine how fast the system has to be to make the query and release you while you pass the 40 km/h there right!?... So that’s where the index comes in, roughly, an internal BD consultation assistant. I even know that you already know everything. So let’s assume you’re traveling, so there in the "Never Stop" comic, you’d be at the end of the "Release Queries" table. Let’s assume that their system is intelligent, and always consult this table when you pass for a release. [...]

  • [...] Even if your table has indexes, it can "start reading" from beginning to end (asc), or from end to beginning (desc), as if you were selecting and reading the results. You make a select and know that what you seek is usually at the end, so use (desc) to "start" your reading... basically this.

  • The idea is that: even with indexes, it follows a sequence. It is better to start at the beginning or at the end!? ... as I only talked 1x about it, a long time ago, so I’m not sure, so I thought it best to comment only.

1 answer

6


I sucked an answer in the OS:

If you usually do much more reverse searches (and this is not common) it may be faster if you use the DESC in the SQL Server edition that supports parallelized searches, since only sequential forward searches can be parallelized. So if you have a ASC and have it consulted DESC, has no parallelization (today).

Mostrando o plano de execução

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

So I would avoid DESC clustered much will be done append and if not use almost exclusively to read backwards.

I don’t really like the database mechanisms because they force certain things on you. I prefer to use them just as Storage and let you do what I want, which can give you more flexibility on how to deal with this.

Browser other questions tagged sql-server índices

You are not signed in. Login or sign up in order to post.