Hi Rami,

Can you please elaborate more on this problem? What are these DBCC commands?

Thanks in advance.

Hi Rami, Nice article.

An excerpt from MSDN: When you create a clustered index, SQL Server reorganizes the data pages so that the rows are logically stored in clustered-index order. SQL Server doesn't necessarily store the data physically on the disk in clustered-index order, but while creating an index, SQL Server attempts to physically order the data as close to the logical order as possible. Each page in an index's leaf level has a pointer to the page that logically precedes the current page and to the page that logically follows the current page, thereby creating a doubly linked list. The sysindexes table contains the address of the first leaf-level page. Because the data is guaranteed to be logically in clustered-index order, SQL Server can just start at the first page and follow the index pointers from one page to the next to retrieve the data in order.

check this link: http://msdn.microsoft.com/en-us/library/aa496062%28v=sql.80%29.aspx

Naomi,

Here, I am creating a clustered index on a table and inserting records "a","c","b". As its clustered index, we might assume that it will store the records in page also in order. So, page contents might be "a","b","c" . However its not true. In page, it will store it as "a","c","b" only. Every page will have slot array at the end indicating the position of the records in the page. Only slot array will be in order. I am proving that by looking into page contents.

DBCC IND : This will returns the pages of index
DBCC Page : By using this we can see the physical content of page.

Lines 1- 4  I was creating a table and inserting some rows. 
Line 6 :  Finding objectid of table, which will be used in DBCC IND statement in next line
Line 8 : It will returns the pages of the table. As we inserted only 3 records, it will return only 2 pages. One is IAM page and another one is Level0 page. Take that PageID
Line 11: To see Page output, we need to on 3604 trace flag. 
Line 12: DBCC Page : this will show page contents

If you observe page contents, in the end it will shows slot array. If you observe slot array, Slot0 and Slot1 and Slot 2 represents the "a","b","c" having address some thing like 96,120,108. It means, records are not stored physically in order within page.

For an insight into database structures be sure to check out MCM Readiness videos for Database Structures:

Database Structures : http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx

Database Structures - Demo Video : http://technet.microsoft.com/en-us/sqlserver/gg313757.aspx

Thanks for sharing the links, Vishal. They are really good resources.