|
|
Posted by Hugo Kornelis on 04/28/06 23:29
On 28 Apr 2006 05:41:26 -0700, John Rivers wrote:
>Hello,
>
>when a clustered index is present the records *are* physically ordered
>on disk to match the index
>
>that is the whole point of a clustered index
Hi John,
Correct.
>and by default a select statement with no ORDER BY will always return
>data in the order of the clustered index (when present)
Incorrect. Damien already pointed out the risk of parallellism.
Another potential issue is an optimization technique MS employs called
"piggybacking" - if a query on another connection is in the middle of a
tbale scan on the table you need, the DB will use the values coming in
for your query as well, then (when the first query's table scan is
finished) restart the scan from start up to where it started to
piggyback. The results would be like 6 - 7 - 8 - 9 - 10 - 1 - 2 - 3 - 4
- 5
This is almost impossible to reproduce in a test environment, but it
MIGHT happen intermittently in a heavily used production DB. Tough lluck
if your app expects the rows to be in order, even without ORDER BY.
>the issue i am trying to highlight concerns the order of the records on
>disk when a *compound* clustered index is present
>
>i have seen cases when it is not as expected
How did you "see" those cases? Using a query reallly doesn't prove
anything. Did you issue DBCC PAGE commands to inspect the actual
contents of the index and data pages?
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|