When asked the size of the largest row that could be placed on to a single SQL Server data page the answer that most give is 8060 bytes. If you try to create a fixed length table with columns that total this length however it throws an error.

Please create a database to run this test and then follow the immediate steps to duplicate this error/warning.

CREATE DATABASE MyDB

CREATE TABLE dbo.test(a CHAR(8000),b CHAR(54))

Msg 1701, Level 16, State 1, Line 1
Creating or altering table ‘test’ failed because the minimum row size would be 8061,
including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Reducing the column lengths to total 8053 bytes allows the table creation.

CREATE TABLE dbo.test(a CHAR(8000),b CHAR(53))
INSERT INTO dbo.test VALUES (REPLICATE('a',8000),REPLICATE('b',53))

Further Investigation

The next question is how this space is actually consumed on the page. Well here is my script which uses DBCC Page and will select the details needed. Although you do not need to look at the byte values unless you really want to. I must however highly recommend that you read Paul Randall’s Blog post called “Inside the Storage -Engine Anatomy of a record” to get a more detailed insight into a records structure.

	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+',2) WITH TABLERESULTS'
	PRINT @pSQL
	EXEC sp_executesql @pSQL

It is pretty easy to look at the output and to reconstruct the following representation:

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

Adding up the all the storage we find that on our 8192 byte page there is 34 bytes towards the end of the record. But what is stored in this 34 bytes? The answer is that it is unused and I can prove this by designing a table to fit exactly two rows on one page.

DROP TABLE test
GO
CREATE TABLE dbo.test(a CHAR(4039))
INSERT INTO dbo.test VALUES (REPLICATE('a',4039))
INSERT INTO dbo.test VALUES (REPLICATE('b',4039))

Again using the DBCC PAGE script I can break this down, but this time it adds up to 8192.

SELECT (96+(1+1+2+4039+2+1+2)+(1+1+2+4039+2+1+2));

Page Header 96 bytes
Status Byte A 1 byte
Status Byte B 1 byte
Null Bitmap Offset 2 bytes
Fixed Length Data n bytes 4039 bytes
Column Count 2 bytes
Null Bitmap 1 byte

Status Byte A 1 byte
Status Byte B 1 byte
Null Bitmap Offset 2 bytes
Fixed Length Data n bytes 4039 bytes
Column Count 2 bytes
Null Bitmap 1 byte

…
Slot Offset 2 bytes
Slot Offset 2 bytes

Therefore it could be said that the maximum possible length of a single row could have been 8087 (8053+34).

Additional Note

If Row Versioning were applied I would need to account for an additional 14 bytes per row so I would need to reduce my column length to 4025. If you created a new database then it should not be there.