Yes, I know, books online is wrong. I really must get ahold of someone on the docs team and get this fixed.
It's trivial to prove, I'll leave that up to you if you like, but consider this:
Logical fragmentation measures how different the logical ordering of the index is from the physical ordering.
This is the percentage of out-of-order pages in the leaf pages of an index. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
If a clustered index was always ordered physically by the key, it would follow (from the definition of logical fragmentation) that a clustered index always has 0% logical fragmentation, which in reality it does not.
If the data rows are sorted in order then that is physical.
Logical order yes, not necessarily physical. The pages are linked by pointers that point to the next page in the logical order. The page does not have to be the next physically contiguous page.
Are you thinking about b-trees?
Both clustered and nonclustered indexes are b-trees, both are sorted logically by their key columns and may also be physically sorted, though that is not guaranteed.
In a clustered table the leaf pages contain the data, not just a pointer to the data. In a non-clustered index in a clustered table the index contains a pointer to the clustered value. In a heap table the non-clustered index has a pointer to File/Sector/Page.
Almost right. File ID, Page Number, slot index comprises the RID for nonclustered indexes on a heap.
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