Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Return Set Ordered by Physical order of rows?

    I did a search and was unable to find any help on this via Google so I am not sure if it can even be done.

    I would like to do a SELECT query and have the return set ordered by the order which the rows are physically stored in the table.

    Specifically, something like:

    Select a.*, b.normalizedColumn
    FROM table a
    JOIN table b
    (a.id = b.id)
    ORDER BY "Physical order of table a rows"


    I believe if there is a clustered index on the table, I can just order by that index and it SHOULD be the physical order of the table.



    (fyi, I would like to see this as I am generating mock data and would like to verify the "randomness" of the data inserted).

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,499
    Technically, this can not be done with SQL Server. You need a hex editor. The reason is that the query engine does not care what order the rows are on the page, as long as it can access any row it is asked for. Even with a clustered index, you can have page splits that send some records well out of the order you may expect them to be in.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,898
    If there is a clustered index, that index will specify the order of the rows within a page, and the logical order of pages within the table. Extents will still be allocated and filled as the engine sees fit, and there is no way to determine the order in which the rows were inserted into the table because the clustered index will override the order of the insert operations.

    The concept of "order" of rows or columns within a relational table is meaningless. Result sets certainly have order for both rows and columns, but tables and views have no order in a truly relational database. Whatever order the database engine imposes is an artifact of the storage engine, that order isn't "built in" logically and the database engine can change them as it sees fit.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    If you put an incrementing identity column on your table, that will give you the exact order in which the data was entered.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,898
    The identity will help most cases, but there are still ways to bamboozle it. Be careful.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,499
    Would the identity column values be considered "randomized"?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    The identity will show the order in which the data was entered, though that might not be the physical order of the data itself. But the entry sequence is what he's looking for.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •