Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

How SQL server stores of the size of the row is greater than 8060 bytes

Last year over TechNet some DBA ask me "How SQL server stores of the size of the row is greater than 8060 bytes", So thought to publish answer with more details here.


Q1.  how SQL server stores of the size of the row is greater than 8060 bytes?
------>from SQL 2005 onwards if row is greater than 8060 bytes then sql server use row-overflow page

Q2. Will the system take new page ?
-----> Yes it takes new pages as per requirement

3. How the system links two different pages ?
-----> To link these pages with object(table) and Iam page will be created for row-overflow
pages and this Iam page will be linked in Iam page chain for table

To prove same I am using code I got from Microsoft Press 2008 Internals


/*Create table to have data more that 8060 bytes */ 
CREATE TABLE dbo.hugerows
(
a varchar(3000),
b varchar(8000),
c varchar(8000),
d varchar(8000));

/*Inserting data much more that 8060 bytes*/

INSERT INTO dbo.hugerows
SELECT
REPLICATE('a', 3000),
REPLICATE('b', 8000),
REPLICATE('c', 8000),
REPLICATE('d', 8000);


/*To determine whether SQL Server is storing any data in row-overfl ow data pages for a
particular table*/
  SELECT object_name(object_id) AS name,
partition_id,
partition_number AS pnum, rows,
allocation_unit_id AS au_id,
type_desc as page_type_desc,
total_pages AS pages
FROM
sys.partitions p JOIN sys.allocation_units a
ON
p.partition_id = a.container_id
WHERE
object_id=object_id('dbo.hugerows');


/* To determine what type of pages sql server have */
 DBCC IND (test, hugerows, -1)

à Refer:  IAM_chain_type, PageType

/****************************************************/
Page types:
■ PageType = 1, Data page.
■ PageType = 2, Index page.
■ PageType = 3, LOB or row-overflow page, TEXT_MIXED.
■ PageType = 4, LOB or row-overflow page, TEXT_DATA.
■ PageType = 10, IAM page.
/****************************************************/