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
Thanks a lot.It work like a charm and saved my time…
Thank you