none
What is the meaning of Ascending\descending sort order of a column within an index

    Question

  • When I add a new index I can also specify the "sort order" as ascending or descending. What does it mean? does it affect performance? does it mean that the binary search tree will be set in a certain way?
    Monday, August 30, 2010 2:57 PM

Answers

  • >>What does it mean?

    It determines whether key data column is stored in ascending or descending order

    >>does it mean that the binary search tree will be set in a certain way? Yes, B+ tree will be arranged accordingly.

    >>does it affect performance? Yes, see please see BOL for detail explanation with an example...

    Index Sort Order

    http://msdn.microsoft.com/en-us/library/ms181154.aspx

    • Edited by Chirag Shah Monday, August 30, 2010 3:16 PM
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:17 AM
    Monday, August 30, 2010 3:01 PM
  • The index leaf level pages are stored in a linked list. The first page in the list is usually the first in the sort order (the lowest value), but if you create an index in DESC order, the first page will be the last in sort order, or the highest value.

    Because the pages are stored in a doubly linked list, in most cases it doesn't matter whether you create the index ASC or DESC. If you ask for data sorted ASC or DESC the link list can be followed in either direction.

    One place it might make a difference is if you have a composite index and want one column sorted one direction and another column sorted another direction. Storing the index in that sequence can avoid the need to sort during querying. Another case would be when the data is inserted in order of the index.


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:17 AM
    Monday, August 30, 2010 3:06 PM
  • It simply means that the data in in the B-Tree will be sorted a specific direction. Specifing an appopriate sort order makes ranged scans faster because the optimizer can look from the front to the back or the back to the front of the index data.  Another thing of note here, is the sort order you specify can help remove sort operations from a query, if an order by clause is used; however, if the sort order does not match that of the index the optimizer will most likely have to perform a sort operation.

    Here is an example,

    IF object_id('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t;
    GO
    
    CREATE TABLE #t(id INT PRIMARY KEY CLUSTERED,col CHAR(1),col2 CHAR(1), Col3 INT);
    INSERT INTO #t VALUES (1,'a','b',5);
    INSERT INTO #t VALUES (2,'d','e',4);
    
    CREATE NONCLUSTERED INDEX ncl_idx_col_col2 ON #t(Col DESC,Col2 ASC,Col3 ASC);
    
    --does not require sort because index key sort is the same as the order by bc optimizer can sort forward and backward
    SELECT *
    FROM #t
    ORDER BY Col DESC,Col2 ASC, Col3 ASC
    
    --requires sort because index key sort is different the order by. 
    SELECT *
    FROM #t
    ORDER BY Col ASC,Col2 ASC,Col3 ASC
    

    http://jahaines.blogspot.com/
    • Edited by Adam HainesModerator Monday, August 30, 2010 3:14 PM syntax
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:17 AM
    Monday, August 30, 2010 3:13 PM

All replies

  • >>What does it mean?

    It determines whether key data column is stored in ascending or descending order

    >>does it mean that the binary search tree will be set in a certain way? Yes, B+ tree will be arranged accordingly.

    >>does it affect performance? Yes, see please see BOL for detail explanation with an example...

    Index Sort Order

    http://msdn.microsoft.com/en-us/library/ms181154.aspx

    • Edited by Chirag Shah Monday, August 30, 2010 3:16 PM
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:17 AM
    Monday, August 30, 2010 3:01 PM
  • The index leaf level pages are stored in a linked list. The first page in the list is usually the first in the sort order (the lowest value), but if you create an index in DESC order, the first page will be the last in sort order, or the highest value.

    Because the pages are stored in a doubly linked list, in most cases it doesn't matter whether you create the index ASC or DESC. If you ask for data sorted ASC or DESC the link list can be followed in either direction.

    One place it might make a difference is if you have a composite index and want one column sorted one direction and another column sorted another direction. Storing the index in that sequence can avoid the need to sort during querying. Another case would be when the data is inserted in order of the index.


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:17 AM
    Monday, August 30, 2010 3:06 PM
  • It simply means that the data in in the B-Tree will be sorted a specific direction. Specifing an appopriate sort order makes ranged scans faster because the optimizer can look from the front to the back or the back to the front of the index data.  Another thing of note here, is the sort order you specify can help remove sort operations from a query, if an order by clause is used; however, if the sort order does not match that of the index the optimizer will most likely have to perform a sort operation.

    Here is an example,

    IF object_id('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t;
    GO
    
    CREATE TABLE #t(id INT PRIMARY KEY CLUSTERED,col CHAR(1),col2 CHAR(1), Col3 INT);
    INSERT INTO #t VALUES (1,'a','b',5);
    INSERT INTO #t VALUES (2,'d','e',4);
    
    CREATE NONCLUSTERED INDEX ncl_idx_col_col2 ON #t(Col DESC,Col2 ASC,Col3 ASC);
    
    --does not require sort because index key sort is the same as the order by bc optimizer can sort forward and backward
    SELECT *
    FROM #t
    ORDER BY Col DESC,Col2 ASC, Col3 ASC
    
    --requires sort because index key sort is different the order by. 
    SELECT *
    FROM #t
    ORDER BY Col ASC,Col2 ASC,Col3 ASC
    

    http://jahaines.blogspot.com/
    • Edited by Adam HainesModerator Monday, August 30, 2010 3:14 PM syntax
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:17 AM
    Monday, August 30, 2010 3:13 PM
  • Thanks, I got it!

    So to summarize:

    the ordering has an affect only with a composite index (that is an index with more than one column).

    Since the leaf nodes are sorted in a doubly linked list, in Adam's example - the following TWO queries do not required a sort operation (and any other sort order combination would required a sort operation):

    following because it is exactly the order of the sort on the non-clustered index:

    SELECT *
    FROM #t
    ORDER BY Col DESC,Col2 ASC, Col3 ASC

    And the following - because it's the reverse order:

    SELECT *
    FROM #t
    ORDER BY Col ASC,Col2 DESC, Col3 DESC

     

     

    Monday, September 13, 2010 6:36 PM