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).

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.

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

If you put an incrementing identity column on your table, that will give you the exact order in which the data was entered.

The identity will help most cases, but there are still ways to bamboozle it. Be careful.

-PatP

Would the identity column values be considered "randomized"?

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.