Having a heap doesn't influence the order.
Both queries return the data in the same random order.
I've asked the question to a lot of people already including MVP's, Microsoft Employees... an answer will come :-)
Unfortunately nobody has an immediate answer :-(
I'll keep you posted!
Subscribe to:
Post Comments (Atom)
2 comments:
I'm going to hazard a guess that, based on your Part III & Part IV blogs, with the NOLOCK hint, the data is scanned in physical page order.
So in a heap, the order would be the same because the data returned from a plain "select * from dbo.foo" would do a table scan in physical page order (same as WITH (NOLOCK)) as there is no logical page order (it's unsorted data).
With a clustered index on the table, the data comes back in the logical order of the index used (i.e. the clustered index) and therefore the logical order of the leaf level pages. But with the table hint it looks like the data is coming back in the same order as the physical pages in the clustered index. So, when you start getting fragmentation in your clustered index, the logical & physical page order start drifting apart and when you rebuild the index the logical & physical order of the pages becomes the same again, and hence the data is returned in the same order regardless of whether you specify the NOLOCK hint or not.
As I said, this is just a guess, but it seems to fit the symptoms you're seeing...I think.
(Of course, anything more complicated than a simple "select * from dbo.foo" and all bets are off!)
Thanks for the comment Mike.
It's seems to be indeed the order of the physical placement. I'm waiting for an answer of one of the SQL Engine developers with more details... I'll post his answer as soon as I get it (if I get it of course :p)
Hmm... let me add a SQL Nerd to my BlogRoll ;)
Post a Comment