Data Row Space Usage: Estimate Row Size Before the Table Grows

Data row space usage is the number of bytes one row takes, and every table size estimate starts there. Know the bytes per row and the rows per day, and you can say how much disk a table needs. This post builds that number from five parts and checks it against what SQL Server reports.

The Sample Table

Four rooms in a garden center, each with an ID, a three-letter code and a name. The code can be NULL, and one room has no code. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Rooms (RoomID INT NOT NULL, Code CHAR(3) NULL, RoomName NVARCHAR(40) NULL);
INSERT INTO Rooms (RoomID, Code, RoomName) VALUES
  (1, 'ORC', N'Orchid Terrace'), (2, 'MAP', N'Maple Reading Lounge'),
  (3, NULL, N'Cedar Annex'), (4, 'OAK', N'Oak Courtyard Garden Room');
GO

Five Parts of a Row

A row is the sum of five parts. The header takes 4 bytes. The fixed data covers every fixed-length column. RoomID is an INT with 4 bytes, and Code is a CHAR(3) with 3 bytes, so 7 in all.

The null block records which columns are NULL. It takes 2 bytes, plus 1 byte for every 8 columns, so 3 bytes for a table with three columns. The variable block takes 2 bytes, plus 2 bytes for each variable-length column, so 4 bytes for one NVARCHAR. The variable data is the text itself, at 2 bytes per character in an NVARCHAR column.

SELECT RoomID, DATALENGTH(RoomName) AS NameBytes,
  4 AS Header, 7 AS FixedData, 3 AS NullBlock, 4 AS VariableBlock,
  4 + 7 + 3 + 4 + DATALENGTH(RoomName) AS EstimatedBytes
FROM Rooms
ORDER BY RoomID;

Orchid Terrace has 14 characters, which is 28 bytes. Add 4, 7, 3 and 4, and row 1 comes to 46 bytes. The four rows come to 46, 58, 40 and 68.

Check It Against SQL Server

An estimate means little until you compare it with the real thing. This dynamic management function reports the smallest, largest and average record size for the table.

SELECT min_record_size_in_bytes AS Smallest, max_record_size_in_bytes AS Largest,
  avg_record_size_in_bytes AS Average, record_count AS Records
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Rooms'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

SQL Server reports 40, 68 and 53.0. The smallest is row 3, and the largest is row 4. The average of 46, 58, 40 and 68 is 53. Every number matches the estimate.

Row 3 has the NULL code, yet it still carries the CHAR(3) bytes. Its size is 4 + 7 + 3 + 4 + 22, which is 40. A NULL in a fixed-length column saves nothing, because the column keeps its full width.

The Null Block Is Always There

Does a table with no nullable columns skip the null block? Test it with the same first room, but every column NOT NULL.

CREATE TABLE RoomsStrict (RoomID INT NOT NULL, Code CHAR(3) NOT NULL, RoomName NVARCHAR(40) NOT NULL);
INSERT INTO RoomsStrict (RoomID, Code, RoomName) VALUES (1, 'ORC', N'Orchid Terrace');

SELECT max_record_size_in_bytes AS RecordBytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RoomsStrict'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

The record is 46 bytes, the same as before. SQL Server keeps the null block even when no column can be NULL.

A NULL in the variable column behaves differently. Try the first room again, with a NULL name.

CREATE TABLE RoomsNoName (RoomID INT NOT NULL, Code CHAR(3) NULL, RoomName NVARCHAR(40) NULL);
INSERT INTO RoomsNoName (RoomID, Code, RoomName) VALUES (1, 'ORC', NULL);

SELECT max_record_size_in_bytes AS RecordBytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RoomsNoName'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

The record shrinks to 14 bytes. With no variable data, the variable block disappears. Only the header, fixed data and null block are left.

From One Row to One Year

Now the planning question. A table gets 100,000 rows a day, and you want to know the disk space for a year. That’s 36,500,000 rows. A page holds 8,096 bytes of rows. Each row also needs a 2-byte slot entry, so a 46-byte row costs 48 bytes.

Load 100,000 rows to see what a day looks like. The rows are the same size as room 1.

CREATE TABLE RoomLog (LogID INT NOT NULL, Code CHAR(3) NULL, RoomName NVARCHAR(40) NULL);
INSERT INTO RoomLog (LogID, Code, RoomName)
SELECT value, 'ORC', N'Orchid Terrace' FROM GENERATE_SERIES(1, 100000);

SELECT record_count AS Records, max_record_size_in_bytes AS RecordBytes, page_count AS Pages
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RoomLog'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

SQL Server reports 596 pages. The formula agrees. 8,096 divided by 48 gives 168 whole rows per page, and 100,000 rows need 596 pages. Now scale the formula to a year.

SELECT CAST(100000 AS BIGINT) * 365 AS RowsInAYear,
  8096 / (46 + 2) AS RowsPerPage,
  CEILING(100000.0 / (8096 / (46 + 2))) AS PagesPerDay,
  CEILING(36500000.0 / (8096 / (46 + 2))) AS PagesInAYear,
  CEILING(36500000.0 / (8096 / (46 + 2))) * 8 / 1024 AS MegabytesInAYear;

A year of rows needs 217,262 pages, which is 1,697 MB, about 1.7 GB. This counts the data rows only. Indexes, free space on pages and row versioning come on top, so buy more disk than the estimate.

Clean Up

Remove the sample tables when you finish.

DROP TABLE RoomLog;
DROP TABLE RoomsNoName;
DROP TABLE RoomsStrict;
DROP TABLE Rooms;

What to Remember

Add the five parts to get the size of a row: header, fixed data, null block, variable block and variable data. Divide 8,096 by the row size plus 2 to get the rows per page. Then divide your row count by the rows per page to get the pages.

To go further, read How Much Space Does a NULL Take? NULL Storage in Rows, Out-of-Row Storage for varchar(max): Keeping Data Pages Small and SQL SERVER – Find Space Used For Any Particular Table.

A row is not only its data, it is the data plus what SQL Server needs to find it.

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