Calculating Record Size of MS SQL Database Tables

This post introduces a query to determine table record sizes of an MS SQL Server database based on the MSDN article Estimating the Size of a Heap (2008). Record size may not be as relevant in SQL Server 2005+ as in previous versions, as the restriction that a record fit in an 8k page has been dropped in SQL Server 2005.

The query consists of 3 CTEs and a final SELECT:

• OCols determines the effective maximum length in bytes for each column
• OColSum counts columns and sums the lengths grouped by table and nullability
• Bytes adds the values of OColSum based on the formulas in the MSDN article.
• SELECT lists the Bytes values and calculates the total record length

WITH 
ocols AS (
	SELECT o.object_id, c.column_id, o.name AS oname, c.name AS cname, 
		t.name AS tname, t.max_length, c.is_nullable,
	CASE 
		WHEN t.name IN ('char', 'binary') AND c.max_length >= 0 
			THEN c.max_length
		WHEN t.name IN ('varchar', 'varbinary', 'nvarchar') 
			AND c.max_length >= 0 THEN c.max_length + 2
		WHEN t.name IN ('nchar') THEN c.max_length / 2
		WHEN t.name IN ('bit') THEN 1
		WHEN t.name IN ('decimal', 'numeric', 'float', 'real') 
			THEN c.max_length
		WHEN t.name IN ('sql_variant', 'xml') THEN 0
		WHEN c.max_length = -1 THEN 0
		ELSE t.max_length
	END AS eff_length
	FROM sys.objects o
	INNER JOIN sys.columns c ON o.object_id = c.object_id
	INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
	WHERE o.schema_id = 1
	AND o.type = 'U'
), 
ocolsum (object_id, column_count, is_nullable, eff_length) 
AS (
	SELECT object_id, count(column_id), is_nullable, sum(eff_length)
	FROM ocols
	GROUP BY object_id, is_nullable
),
Bytes AS (
	SELECT o.object_id, o.name, 
		ISNULL(ocolsum.column_count,0) + 
			ISNULL(ocolsumnull.column_count,0) AS NumCols,
		ISNULL(ocolsum.eff_length,0) AS FixedDataSize,
		ISNULL(ocolsumnull.column_count,0) AS NumVariableCols,
		ISNULL(ocolsumnull.eff_length,0) AS MaxVarSize,
		2 + ((ISNULL(ocolsum.column_count,0) + 
			ISNULL(ocolsumnull.column_count,0)  + 7) / 8 ) 
				AS NullBitmap,
		2 + (ISNULL(ocolsumnull.column_count,-1) * 2) + 
			ISNULL(ocolsumnull.eff_length,0) AS VarDataSize
	FROM sys.objects o
	LEFT OUTER JOIN ocolsum 
		ON o.object_id = ocolsum.object_id 
		AND ocolsum.is_nullable = 0
	LEFT OUTER JOIN ocolsum ocolsumnull 
		ON o.object_id = ocolsumnull.object_id 
		AND ocolsumnull.is_nullable = 1
	WHERE o.type = 'U'
)
SELECT name, NumCols, FixedDataSize, NumVariableCols, MaxVarSize, 
	NullBitmap, VarDataSize,
	FixedDataSize + VarDataSize + NullBitmap + 4 AS RowSize
FROM Bytes
ORDER BY name