Looking into SQL Server page types

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server pages and what types of pages exist.

I had to explain to them about SQL Server architecture first. Each data file is divided internally into pages.Each page has a size of 8 kbytes.

In each of these pages (data,metadata,indexes) are stored. 8 continuous pages of 8kbytes make up one extent which is 64kbytes.
If you make the calculations,16 extents make up 1 mbyte.

Extents are divided into 2 categories,

• Uniform – 8 pages belong to the same object e.g table
• Mixed  – 8 pages do not belong to the same object

The first 96 bytes of the page are also reserved for the page header.
Page header includes information like

• Page number
• Page type
• Free space per page

The greatest size in bytes I can have in a page is 8060 bytes
So the maximum record length I can have in a table is 8060 bytes

If I have a table that its record length is 100 bytes, that means I can have 80 records per page.
If I have 200 records I need 3 pages. Each record belongs to only one page. You cannot  have half record in one page and another half in another.

Ι was asked to provide an example where we will see the various SQL Server page types and what happens when the row size is larger than 8060 bytes.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

CREATE TABLE largetable
( col1 VARCHAR(2000),
 col2 VARCHAR(8000),
 col3 VARCHAR(max)

 );

INSERT INTO largetable VALUES
(
REPLICATE('SQL Server 2012 will provide Mission Critical
Confidence with greater uptime blazing-fast performance and enhanced
security features for mission critical workloads',12), REPLICATE('SQL Server 2012', 533),
REPLICATE('SQL Server indexes', 1000))



SELECT * FROM largetable
-- Shows types of pages available inside SQL Server

SELECT OBJECT_NAME (object_id) AS tablename, rows,
 type_desc AS page_types_SQL
FROM sys.partitions part
INNER JOIN sys.allocation_units alloc
ON part.partition_id = alloc.container_id
WHERE object_id = object_id('largetable');

Let  me explain what I am doing in this bit of code

• I make the tempdb the current database
• I create a new table called largetable in the tempdb database. As you can see the record length is much larger than 8060 bytes.
• Insert some values into the table. I insert thousands of rows using the REPLICATE function.
  
• Finally I execute a SELECT JOIN statement from the sys.partitions and sys.allocation_units table.

The results are:

tablename    rows               page_types_SQL
largetable       1                            IN_ROW_DATA
largetable       1                            LOB_DATA
largetable       1                            ROW_OVERFLOW_DATA

As you can see I have one row of data.Each of these types of pages is called an Allocation Unit.

IN_ROW_DATA refers to the situation where we have the whole row size staying within the 8,060-byte limit.

LOB_DATA refers to LOB data type columns, in this case  the NVARCHAR(MAX) column and its values.

ROW_OVERFLOW_DATA refers to the data that could not be stored to single page for a single row and that is why it is using an overflow page.

Hope it helps!!!