SQL Server Max Row Size 8060 Part 2

In my previous post on row maximum length I showed that the largest row size that could be stored on page was 8053 byte.

So to recap a table with 2 fixed length columns CHAR(8000) & CHAR(53) the internal storage would look as below, with an unused space of 34 bytes.

Page Header 96 bytes
Status Byte A 1 byte
Status Byte B 1 byte
Null Bitmap Offset 2 bytes
Fixed Length Data n bytes 8000+53 = 8053 bytes
Column Count 2 bytes
Null Bitmap 1 byte
??? 34 bytes
Slot Offset 2 bytes

Well lets now look at variable length columns

CREATE DATABASE MyDB
GO
CREATE TABLE dbo.test(a VARCHAR(8000),b VARCHAR(53))
GO
INSERT INTO dbo.test VALUES (REPLICATE('a',8000),REPLICATE('b',53))

This time I have altered the script to use output 3 from DBCC PAGE. The reason I have done this is to show that this row will no longer fit in row.

 DECLARE @DatabaseName SYSNAME
 DECLARE @ObjectName SYSNAME
 SET @DatabaseName = DB_NAME()
 SET @ObjectName = 'dbo.test'

--1. locate file/page details
 DECLARE @FirstPage BINARY(6),@FileNumber VARCHAR(3),@PageNumber VARCHAR(10),@IndexID INT
 SELECT @FirstPage = a.[first_page],@IndexID = p.[index_id]
 FROM sys.partitions p INNER JOIN sys.system_internals_allocation_units a ON p.[partition_id] = a.[container_id]
 WHERE [object_id]=OBJECT_ID(@ObjectName) AND [type_desc] = 'IN_ROW_DATA'
 SET @FileNumber =(CAST(SUBSTRING(@FirstPage,6,1) AS INT)*256)+CAST(SUBSTRING(@FirstPage,5,1) AS INT)
 SET @PageNumber =(CAST(SUBSTRING(@FirstPage,4,1) AS INT)*16777216)+(CAST(SUBSTRING(@FirstPage,3,1) AS INT)*65536)+(CAST(SUBSTRING(@FirstPage,2,1) AS INT)*256)+(CAST(SUBSTRING(@FirstPage,1,1) AS INT))

--DEBUG INDEX INFO
 SELECT @PageNumber,i.[index_id],i.[type_desc],i.[name] FROM sys.indexes i WHERE i.index_id = @IndexID AND i.[object_id] = OBJECT_ID(@ObjectName)

--2. use dbcc page to retrieve page data
 DECLARE @pSQL NVARCHAR(MAX)
 SET @pSQL = 'DBCC Page (['+@DatabaseName+'],'+@FileNumber+','+@PageNumber+',3) WITH TABLERESULTS'
 PRINT @pSQL
 EXEC sp_executesql @pSQL

By looking at the output from DBCC PAGE you can see that unless the variable length column b is reduced in size from 53 to 47 this row will not fit on the page. I have taken the last few lines from the DBCC PAGE outputs and combined them in the image below.

In the top half of the image you can see that one of the columns has been pushed off into a new page, however in the second half of the image you can see that the complete row now fits.

The 6 bytes gained by reducing the column length from 53 to 47 are used to store the variable column count as well as the column offset array.

Page Header 96 bytes
Status Byte A 1 byte
Status Byte B 1 byte
Null Bitmap Offset 2 bytes
Fixed Length Data n bytes 0 bytes
Column Count 2 bytes
Null Bitmap 1 byte
Variable Length Column Count 2 bytes <– We are using variable length columns so we incur 2 bytes cost
Column Offset Array 2 * 2 = 4 bytes <– for every column we incur 2 bytes for the column offset array
Variable Columns
Slot Offset 2 bytes

One final question… What is the largest single column that can be stored on one page?

If I create this table with a VARCHAR(MAX) what is the most data I can push into it before it is pushed off row? = 8000

DROP TABLE dbo.test
GO
CREATE TABLE dbo.test(a VARCHAR(MAX))
DECLARE @v VARCHAR(MAX)
SET @v = REPLICATE('a',8000)
SET @v = @v + 'z'
INSERT INTO dbo.test VALUES (@v)

Leave a Reply

Your email address will not be published. Required fields are marked *