Is Clusterd Index sort and store data in physical order in table?

Question

Is Clusterd Index sort and store data in physical order in table?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Tim Walker. (10/11/2011)
You are confused because the article you refer to uses confusing terminology.
It starts "A clustered index determines the physical order of data in a table".
This means that if your table occupies 100 pages, the lowest key values will be in page 1 and the highest in page 100 in the database.

Unfortunately that's also wrong. The MSDN article is incorrect.

The index defined the logical order. The index has pointers to the pages that allow an in-order read. There is absolutely no guarantee that the lowest key values will be on page 1 and highest on page 100. The lowest could be on page 56, the next set on page 24 then 99 then 1, etc, etc. There is also no guarantee that the rows will be in-order on the pages.

Indexes only enforce logical order, they make absolutely no promises about the physical order of the pages in the file. SQL will try to put the pages down such that the logical and physical order matches, but it may not be able to.

In fact, the definition of index fragmentation is the % of pages where the logical and physical order don't match. Hence, since indexes can have a fragmentation above 0%, it should be clear that logical and physical order don't always match.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 2

GilaMonster (10/11/2011)
Tim Walker. (10/11/2011)
You are confused because the article you refer to uses confusing terminology.
It starts "A clustered index determines the physical order of data in a table".
This means that if your table occupies 100 pages, the lowest key values will be in page 1 and the highest in page 100 in the database.
Unfortunately that's also wrong. The MSDN article is incorrect.
The index defined the logical order. The index has pointers to the pages that allow an in-order read.
There is absolutely no guarantee that the lowest key values will be on page 1 and highest on page 100. The lowest could be on page 56, the next set on page 24 then 99 then 1, etc, etc. There is also no guarantee that the rows will be in-order on the pages.

Sorry, that's what I meant. The begining or first page of the index might actually be on page 56 but it's still where the clustered index starts and contains the lowest values. The next page pointed to (which might be on page 23) has the next lowest values until you get to the end.

I'm assuming the examples we both gave are deliberately ignoring uniform extents, which would of course place these pages in blocks of 8. 😉

Tim

.

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 3

Gail,

Just to clarify- is this "The index defined the logical order " always true?

I mean cases when you create or rebuild clustered index (still no physical order?)

Thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Of course it's true, that's one of the fundamental points of an index, that the data at the leaf level is logically ordered by the key columns. If it wasn't, it would be useless for finding data in.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 5

Sorry for the response delay (was extremely busy during office hours)-

just to clarify what I actually meant asking my question-

when clustered index was just created or rebuit- physical and logical orders are not the same

(or in sync- not sure what is better)?

Thanks, Yuri

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

GilaMonster (10/12/2011)
Of course it's true, that's one of the fundamental points of an index, that the data at the leaf level is logically ordered by the key columns. If it wasn't, it would be useless for finding data in.

I also would like to know that what should happen if I am creating new index or rebuilt the index? Physical and logical orders should be in sync. isn't it?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQL will try to put the pages down in physical order within the data file, but it is by no means guaranteed that it will be able to do so, and it is certainly not guaranteed that it will stay that way.

When last did you create or rebuild an index and find the logical fragmentation immediately thereafter at exactly 0%?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass