If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Return Set Ordered by Physical order of rows?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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).
Reply With Quote
  #2 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,446
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.
Reply With Quote
  #3 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,740
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.
Reply With Quote
  #4 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,556
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
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,740
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.
Reply With Quote
  #6 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,446
Would the identity column values be considered "randomized"?
Reply With Quote
  #7 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,556
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On