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
ocols AS (
	SELECT o.object_id, c.column_id, AS oname, AS cname, AS tname, t.max_length, c.is_nullable,
		WHEN IN ('char', 'binary') AND c.max_length >= 0 
			THEN c.max_length
		WHEN IN ('varchar', 'varbinary', 'nvarchar') 
			AND c.max_length >= 0 THEN c.max_length + 2
		WHEN IN ('nchar') THEN c.max_length / 2
		WHEN IN ('bit') THEN 1
		WHEN IN ('decimal', 'numeric', 'float', 'real') 
			THEN c.max_length
		WHEN 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,, 
		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
		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

2 Responses to Calculating Record Size of MS SQL Database Tables

  1. Arun Ramanantham says:

    Thanks a lot.It work like a charm and saved my time…

  2. Polly says:

    Thank you

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: