Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.B One row can span many row-overflow pages.

Read Previous Article:

Special Storage Series – I (Checking the 8K (8192) size of page)

Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A

Here in this article we will take on with the previous article to see how one row can span many row-overflow pages if it contains many large variable length fields in the table. Lets take a look at a table with each variable length data type column size of more than 8K bytes.

create table Tab_VKM_BigTable_II

(col1 varchar(3000),

col2 varchar(8000),

col3 varchar(8000),

col4 varchar(8000))

Command(s) completed successfully.

Our table is ready whose maximum record size to store is greater than 27000 byte. Let’s insert data with size more than 27K byte.

insert into Tab_VKM_BigTable_II

select REPLICATE('a',3000),REPLICATE('b',8000),

       REPLICATE('c',8000),REPLICATE('d',8000)

(1 row(s) affected)

Now we have record whose length is more than 27000 bytes. You can query the table to see the record

select * from  Tab_VKM_BigTable

Now we will introspect the table storage detail, i.e. In what all kind of page, data of table are getting stored by querying System Catalog   sys.partitions and sys.allocation_units.

sys.partitions will hold a single entry irrespective of whether you apply partitioning on table or not. It will give Partition Id and Partition Number and number of rows in the table. Partiton number is 1 for Non Partitioned   Table.

sys.allocation_units will hold information about how data are allocated on various pages like page Type, number of Pages etc.

select OBJECT_NAME(p.object_id) as TableName,

p.partition_id,p.partition_number,p.rows,

au.total_pages as Pages,

au.type_desc as PageType

from sys.partitions p, sys.allocation_units au

where p.partition_id=au.container_id

and p.object_id=OBJECT_ID('Tab_VKM_BigTable_II')

TableName           partition_id         parti_no rows Pages PageType

------------------- -------------------- -------- ---- ----- -----------------

Tab_VKM_BigTable_II 72057594042449920    1         1   2     IN_ROW_DATA

Tab_VKM_BigTable_II 72057594042449920    1         1   4     ROW_OVERFLOW_DATA

(2 row(s) affected)

Now from the output we can see there are four pages for row-overflow, one for row-overflow IAM and three for data that doesn’t fit in regular row. We can fire DBCC IND query to and see the detail of each pages.

Here we will be using the table TAB_VKM_TablePages which we have created in our last post (Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.A) to store the result of DBCC IND.

insert into TAB_VKM_TablePages

exec ('dbcc ind (''Sparsh'',''Tab_VKM_BigTable_II'',-1)')

(6 row(s) affected)

Now let’s query the TAB_VKM_TablePages to see the DBCC IND data for the table Tab_VKM_BigTable_II

select PageFID,PagePID,OBJECT_NAME(objectId) as [Table],

iam_chain_type,PageType from TAB_VKM_TablePages

PageFID PagePID Table               iam_chain_type     PageType

------- ---------------------------------------------- --------

1       369     Tab_VKM_BigTable_II In-row data        10

1       368     Tab_VKM_BigTable_II In-row data        1

1       357     Tab_VKM_BigTable_II Row-overflow data  10

1       356     Tab_VKM_BigTable_II Row-overflow data  3

1       358     Tab_VKM_BigTable_II Row-overflow data  3

1       359     Tab_VKM_BigTable_II Row-overflow data  3

(6 row(s) affected)

From the output we can observe there are two pages for In-Row Data, one is IAM and another is data page. And there are four pages for Row-Overflow Data, one is IAM page and other three are used to store the data.

Conclusion: One row can span many row-overflow pages.

It’s fun to be around Code that has wonderful potency to bring life to tranquil object.

Post Reference: Vikram Aristocratic Elfin Share