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)