I looked at your quiz questions for SQL Server. For the second question, there is an index created on a table and then a select statement that filters on the first column in the index and orders by the second in descending order. But in the create index statement, the index is storing the values for the second column in the default order which is ascending. I thought that an improvement could be made so that the query wouldn't have to re-order the results from ascending in the index to descending as requested by the order by. This could be done by changing the index to store the second column in descending order. Why was this incorrect?

asked Oct 21 '13 at 23:28

bwhiteips's gravatar image

bwhiteips
21115

edited Oct 21 '13 at 23:31


One Answer:

Hi!

It's incorrect because SQL Server can read an existing index in both directions anyway (ASC and DESC). That means, if the index is created in the exact opposite order as requested by the ORDER BY clause, the DB will just read the index in the other direction. Still no sorting needed.

See also: http://use-the-index-luke.com/sql/sorting-grouping/order-by-asc-desc-nulls-last

answered Oct 22 '13 at 08:31

Markus%20Winand's gravatar image

Markus Winand ♦♦
93651120

Markus that makes sense to me thanks for the clarification!

(Oct 22 '13 at 19:28) bwhiteips