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.