locked
Order of the records in a table RRS feed

  • Question

  • This is a general question,

    1.) How is the order of the records in a table with no indexes?

    2.) How is the order of records in a table with only one clustered index?

    3.) How is the order of records in a table with only one non-clustered index?

    4.) How is the order of records in a table with only one clustered index and multiple non-clustered index?

    Thanks in advance for the help.


    svk


    • Edited by czarvk Monday, August 11, 2014 7:10 PM
    • Moved by ArthurZ Monday, August 11, 2014 7:23 PM Does not appear SSIS related
    Monday, August 11, 2014 7:10 PM

Answers

  • In general, there's no "order oft he records in a table".

    Data are stored on disc in a logical order (pages/extents) determined by either the clustered index or, if it's a heap, in the order the data were inserted (inserts in heaps will use the next page that's available).

    So, to answer each and every scenario you posted: random.

    Any non-clustered index will not affect the logical order. It's a separate reference to quickly identify the page where the requested data are located.

    The order of rows returned by a SELECT statement can only be deterministic when using an ORDER BY clause followed a column or a list of columns that allow to uniquely identify a row. Anything else is more or less random...

    Monday, August 11, 2014 7:29 PM

All replies

  • In general, there's no "order oft he records in a table".

    Data are stored on disc in a logical order (pages/extents) determined by either the clustered index or, if it's a heap, in the order the data were inserted (inserts in heaps will use the next page that's available).

    So, to answer each and every scenario you posted: random.

    Any non-clustered index will not affect the logical order. It's a separate reference to quickly identify the page where the requested data are located.

    The order of rows returned by a SELECT statement can only be deterministic when using an ORDER BY clause followed a column or a list of columns that allow to uniquely identify a row. Anything else is more or less random...

    Monday, August 11, 2014 7:29 PM
  • >1.) How is the order of the records in a table with no indexes?

    Unknown, the database engine takes care of it.

    2.) How is the order of records in a table with only one clustered index?

    As defined in the index: http://technet.microsoft.com/en-us/library/aa933131(v=sql.80).aspx

    3.) How is the order of records in a table with only one non-clustered index?

    Unknown, the database engine takes care of it. NCI is a separate tree structure from the data.

    4.) How is the order of records in a table with only one clustered index and multiple non-clustered index?

    As defined in the clustered index:  http://msdn.microsoft.com/en-us/library/ms190457.aspx

    BOL: "Consider using a clustered index for queries that do the following:

    • Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

      After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. For example, if a query retrieves records between a range of sales order numbers, a clustered index on the column SalesOrderNumber can quickly locate the row that contains the starting sales order number, and then retrieve all successive rows in the table until the last sales order number is reached.

    • Return large result sets.

    • Use JOIN clauses; typically these are foreign key columns.

    • Use ORDER BY, or GROUP BY clauses.

      An index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Database Engine to sort the data, because the rows are already sorted. This improves query performance."

    LINK: http://technet.microsoft.com/en-us/library/ms190639(v=sql.105).aspx


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012










    • Edited by Kalman Toth Monday, August 11, 2014 7:46 PM
    • Proposed as answer by JRStern Monday, August 11, 2014 8:08 PM
    Monday, August 11, 2014 7:36 PM
  • You might also want to be aware that no matter what the physical order of the rows in the table, any select statement may still return them in random order unless you specify an "order by" clause.

    Josh

    Monday, August 11, 2014 8:09 PM
  • 2.) How is the order of records in a table with only one clustered index?

    As defined in the index: http://technet.microsoft.com/en-us/library/aa933131(v=sql.80).aspx

    Not necessarily if you traverse the disk in sector order. Nor if you traverse the database file in page order. Only if you traverse the table pages in the order defined by the next pointers in the leaf level of the index you are guaranteed to see the rows in this order.

    That is, logically, there is on order at all in a table, because it is a set. Physically, well define which physical order you are asking about first!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 11, 2014 9:46 PM