Interview Question of the Week #002 – Script to Find Byte Size of a Row for All the Tables in Database

I was on an interview panel when someone asked for the byte size of a row. I was surprised. I thought the question had become less important as databases grew, but an interviewer still expects a precise answer. The first thing I would ask is: Do you mean a table’s declared maximum, the data stored in a particular row, or the complete physical record?

Question: How can I find row size in bytes for tables in SQL Server?

Answer: Sum sys.columns.max_length for each table. That gives you a declared-length summary, not the actual byte size of every row. Also remember that SQL Server still has an 8,060-byte in-row limit, with row-overflow storage for some variable-length data.

The query below treats the -1 marker for varchar(max), nvarchar(max), varbinary(max) and xml separately, instead of adding it as a negative byte count:

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS TableName,
       SUM(CASE WHEN c.max_length >= 0
                THEN CONVERT(bigint, c.max_length) ELSE 0 END)
           AS BoundedDeclaredBytes,
       SUM(CASE WHEN c.max_length = -1 THEN 1 ELSE 0 END)
           AS LargeValueColumns
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.columns AS c ON c.object_id = t.object_id
WHERE t.is_ms_shipped = 0
  AND c.is_computed = 0
GROUP BY s.name, t.name
ORDER BY s.name, t.name;

max_length is measured in bytes. The sum above doesn’t include every physical-record overhead, and it doesn’t tell you how many bytes an individual varchar value occupies. A table with varchar(100) can store a three-byte value in one row and a much longer value in another.

For a particular value, DATALENGTH answers a narrower question:

SELECT DATALENGTH(CAST('ABC' AS varchar(20))) AS StoredValueBytes;

This returns 3 for that expression, not a complete physical record size. That distinction is the interview answer I want to hear. The catalog query measures the declared side of the question; it doesn’t measure every stored row.

Row size is not one number, it is a declared maximum and a stored value, and a good answer names both.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.