Hi rick.white, Ah, you noticed our new article rating system ;-) Actually this is a bug, and I'll report it to our Web team ASAP. (I'll let you know if it's database related :-) Thanks for pointing it out--and for being good-humored about it.
Anne Grubb, Web site strategic editor, SQL Server Magazine

Anne2/5/2008 9:47:23 AM

Check out this page with Firefox 2.0; I see, under Rate this Article, the scale (left to right) 1 1 3 1 5.

If this is intentional given the article's topic, it's hilarious. I rated it a 1, and by 1, I mean 4. At least I think that's what I rated.

Rick2/4/2008 10:34:07 PM

Hi Itzik,

Regarding the Logical Fragmentation : it is the pages in the leaf level are organized in a doubly linked list, maintaining logical index key ordering. Besides the linked list, SQL Server also maps the leaf level’s data with IAM pages in file order. The logical order of the pages maintained by the linked list (index key order) can be different from the order of the pages in the files. The discrepancy between index order of pages and file order of pages is called logical fragmentation.

Does SQL Server maintain altogether a separate file to Map the leaf level Page with IAM Page ? As per my understanding then it should refer the physical address of the linked Page from the referencial Page (Page which holds the address of the linked Page) and put it into the file. And there is all probability for the out of order Page appearance. In that scenario how the Index defragmentation facility of SQL Server comes into play ?

I am bit of confused. Need your help to carify.

Thanks & Warm Regards,
Arindam Ganguly.

Arindam12/21/2007 10:50:09 AM

Hi Itzik,

One more thing, having read your article, I conclude that when Ordered is true, it indicates index-order scan, and when Ordered is false, it probably indicates either index-order scan or allocation-order scan, is my conclusion correct?

Thank you.

Kasim Wirama

[Reply:

Yes, you understood correctly.

Itzik]


Kevin12/2/2007 4:11:01 PM

Hi Itzik,

I am suprised with the fact you find, it sounds make sense. I would like to know whether page split might happen where the primary key is an identity column such as in sales.salesorderheader.

Thank you.

Kasim Wirama

[Reply: Hi Kasim,

As I mentioned in the article, when the new row has a higher index key value than the highest existing key, SQL Server won’t split the page. Still, the fact that there are no splits in indexes on ever-increasing keys, you might still get splits in other indexes.

Itzik]

Kevin12/2/2007 3:46:42 PM