Hi Friends,
Continuing on from my last blog post “SQL Server Clustered Index Physically Orders a table”, this post demystifies the myth “Clustered index physically a table”.
The order of the rows in a page is managed by Row offset table and not by the SQL Server clustered index. Row offset table defines the scope of a row in page i.e. it tells the start of a particular row in a page. Here is an abstract about row offset table from books online. (http://msdn.microsoft.com/en-us/library/aa174529(v=sql.80).aspx)
“The row offset table contains one entry for each row on the page and each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.”
Let’s now look into this in more detail. The code below creates a test table and populates it with values.
Create table tbltest
(
Sno int Primary Key,
Col1 char(1000)
)
GO
Insert into tbltest
values(4,'D'),(5,'E'),(6,'F')
Let’s now look at the page structure of tbltest and figure out what’s an offset table looks like. I used DBCC IND to get all page ids for tbltest as shown below.
The page we are interested in is #pageid=264. I used DBCC Page to get the offset table for #pageid 264 as shown below.
The “Starting bytes of a slot” tells how far the first byte of the row is from the page. Row 0 is 96 bytes far from the start of the page where 96 bytes is the page header size. The “Starting bytes of slot” for subsequent rows are calculated as [Current Row Size] + [Previous Row Size]. Hence for Row 1 its 96+1011=1107 and for Row 2 its 1107+1011=2118.
With that being said, let’s add a new record and analyze the change in offset table as shown below.
The new record added is 3129 bytes far from the start of the page but has been mapped to slot 0 to implement the clustered index order. The physical location of existing slots is not changed rather offset table is changed as per the clustered index order.
Thus, clustered index doesn’t physically orders the data in the table rather it’s the offset table that does that ordering and clustered index only tells the order to be maintained to offset table.
Note: DBCC IND AND PAGE are undocumented commands to get page structure. More information on them is available on internet.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
hi, nice article.
But, i don’t understund one aspect: why this row have phisically 1011 bytes instead of 1004 (1000+4 int)?
Quite a finding indeed!!!
That means the leave node of a Clustered Index has rowoffset!!!1
is that so?
Regards,
Jeevan Anaparthy,
Sr Software Developer
CSC, Chennai
Hi Kamil – Thats the record size dbcc page returned.. will try to figure it out how its different from row size.
Hi Jeevan – nopes.. leaf node of a clustered index contain data pages and data pages contain row offset table.
Hi Kamil – apart from actual data, a row also stores some additional information as in status bit A and B, Fixed length size,number of columns,NULL bitmap, number of variable length columns in a row and variable column offset array. for more details refer to any Inside sql server internal book from Kalen Delaney
Hi Ahmed,
It was really a gud article, so is it always the case that clustered index never physically ordered the underlying data based on index keys..or it does so in some cases ?
also you are entering new records in a table already having a clsutred index, what would be the scenario if we are creating clustered index on table already have some records, does it re-organizes data or not ?
Thanks Rajesh. yes it is always the case. I would suggest you to try out the other case and post your findings.. 🙂
Hi Ahmad Sir,
Yes, I tried and found that clustered index do physical order the records, but again it depends.
two scenario-
1) if records are already present in the table and then we create a clustrd indx, in this case it will physically ordered the records, as this can be make sure since data pages id’s before the index and after the index changes.
2) butwhen we insert rows in a table having a clustrd index, then you r right, it’ll not physcally ordred the table rather adjusted the offset for each recors in a page.
Thanks,
Rajesh.
great try Rajesh… can you please share the results..
sure, I’ll share the result.
Hi Ahmed,
I have saved my result in a word doc with screnshots, not able to paste images here, can you give me your email id’s.
ahmad.osama1984@gmail.com