Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.C Update statement on row-overflow pages data.

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
• Special Storage Series – II Restricted-Length Object Data (Row Overflow Data) – II.B One row can span many row-overflow pages.

Here in this article we will take on with the previous article to see the affect of Update statement on data rows which are spanning in many row-overflow pages.

Let’s take a look at a table with each variable length data type column size of more than 8K bytes.

create table Tab_VKM_BigTable_III

(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_III

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_III')

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.

Now we will fire Update statement on col2 of the table Tab_VKM_BigTable_III and reduce the size of the column data to 3000 from 8000 byte.

update Tab_VKM_BigTable_III

set col2 = REPLICATE('b',3000)

(1 row(s) affected)

Let’s query now sys.partitions and sys.allocation_units to see the effect of Update Query

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_III')

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   3     ROW_OVERFLOW_DATA

(2 row(s) affected)

Here we can see one ROW_OVERFLOW_DATA is removed so now it 3 row-overflow data pages where one is IAM and other two are used to store data.

Lets now again update the col2 and col3 to 1 byte data and check the data span over Pages.

update Tab_VKM_BigTable_II

set col2 = REPLICATE('b',1)

,col3 = REPLICATE('c',1)

Let’s query now sys.partitions and sys.allocation_units to see the effect of Update Query

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_III')

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   2     ROW_OVERFLOW_DATA

Here we can see one ROW_OVERFLOW_DATA is removed so now it 2 row-overflow data pages where one is IAM and other one is used to store data

Conclusion: Update statement on row-overflow pages data cause the number of pages reduced or increased.

Programmer just wanted to play with code; this is the highest honor that can ever be paid and its mind blowing.

Post Reference: Vikram Aristocratic Elfin Share