CODEWITHSUNDEEP
sql-servert-sql
Allan Xu
Allan Xu

Reputation: 9358

Is there any performance difference between ASC and DESC when ORDER BY clause is properly indexed?

Let's say the tableA has a clustered index on Column1

Is there any performance impact differences between the following statements:

SELECT TOP 5 Column1 FROM tableA  ORDER BY Column1 ASC

SELECT TOP 5 Column1 FROM tableA  ORDER BY Column1 DESC

Update, and the catch

@TheGameiswar answer is correct. However, there is a big catch where there are multiple columns in the order by clause:

SELECT TOP 5 Column1 FROM tableA  ORDER BY Column1 , Column2 DESC

actually means 

SELECT TOP 5 Column1 FROM tableA  ORDER BY Column1 ASC, Column2 DESC

Above statement imposes a Sort query processor operator to the plan and makes the query very inefficient.

So, make sure the descending sort order includes "DESC" for both columns

SELECT TOP 5 Column1 FROM tableA  ORDER BY Column1 DESC , Column2 DESC

Upvotes: 3

Views: 1371

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28910

No difference or performance penalty..

SQL server will use an index if available(for the query you used ) to do scan backwards or forwards depending on sorting..

I have a numbers table which has index on number column like below.Below is the definition of index..

CREATE UNIQUE CLUSTERED INDEX [n] ON [dbo].[Numbers]
(
    [Number] ASC
)

As you can see above index is by created with ASC option(which is default)..now lets see how database engine deals with below query 

select top 10* from numbers
order by number desc

enter image description here

You can see SQL decided to scan the index backwards..

Upvotes: 4

Related Questions