SQL Server堆表行大小疑问：估算值与实际查询结果不符

Great question! Let's break down how that 52-byte record size is calculated—SQL Server adds several hidden metadata overheads to every heap row that aren't always obvious when you just sum up your column sizes.

The Anatomy of a Heap Row #

Every row in a heap table has three core components that contribute to its total size:

• Fixed row header overhead
• Optional null bitmap (if applicable)
• Actual column data

1. Fixed Row Header (Non-Negotiable) #

No matter what your table structure looks like, every heap row starts with a 4-byte base header:

• 1 byte for Status Bits A: Tracks basic row state (e.g., if the row is deleted, has a forward pointer)
• 1 byte for Status Bits B: Tracks extra state (e.g., if row versioning is enabled for this row)
• 2 bytes for Total Length: Stores the full byte count of the entire row (header + data)

2. Optional Row Version Pointer #

If your database has snapshot isolation or read committed snapshot isolation enabled, SQL Server adds an extra 14-byte version pointer to the row header. This tracks the row's version history for consistent reads. With this enabled, the total header jumps to 4 + 14 = 18 bytes.

3. Optional Null Bitmap #

A null bitmap is added if your table has:

• Any nullable columns, OR
• Any variable-length columns (like varchar, nvarchar, varbinary)

The size of the bitmap is calculated as CEILING(number_of_columns / 8) bytes. For example, 9 columns would need 2 bytes (since 9/8 = 1.125, rounded up to 2).

4. Column Data Size #

This is the sum of your actual column storage sizes:

• For fixed-length columns (e.g., int, char, datetime), each column uses its full defined size every time
• For variable-length columns, if every row stores the exact same length of data (e.g., a varchar(50) that always holds 20 bytes), the min/max record sizes will match.

Breaking Down the 52-Byte Size #

Since both MinimumRecordSize and MaximumRecordSize are 52 bytes, all rows in your heap are identical in length—this means you either have all fixed-length columns, or variable-length columns that never change size. Here are the two most likely scenarios:

Scenario 1: No Row Versioning Enabled #

If snapshot isolation isn't turned on, the base header is 4 bytes. Assuming you have no null bitmap (no nullable or variable-length columns), your column data adds up to 52 - 4 = 48 bytes. Examples of this could be:

• 12 int columns (12 × 4 = 48 bytes)
• A single char(48) column
• Any combination of fixed-length columns that sum to 48 bytes

Scenario 2: Row Versioning Enabled #

If snapshot isolation is active, the header jumps to 18 bytes. Subtract that from 52, and your column data totals 34 bytes. For example:

• 8 int columns (8 × 4 = 32) + 1 smallint (2 bytes) = 34 bytes
• A single char(34) column

If you do have nullable/variable-length columns, you'll need to add the null bitmap size to the mix. For example, a 1-byte bitmap (for up to 8 columns) would mean your column data is 52 - 4 - 1 = 47 bytes.

How to Verify This #

To confirm the math, run this query to get your table's column details:

SELECT 
    c.name AS column_name,
    t.name AS data_type,
    c.max_length,
    c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('YourTableName');

Sum up the max_length values for fixed-length columns (or the actual used length for variable-length if you know it), add the header and bitmap overhead, and you'll land exactly on 52 bytes.

You can also dig deeper with this index stats query to see heap-specific details:

SELECT 
    OBJECT_NAME(object_id) AS table_name,
    minimum_record_size_in_bytes,
    maximum_record_size_in_bytes,
    record_size_in_bytes
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('YourTableName'),
    0, -- Heap tables have index ID 0
    NULL,
    'DETAILED'
);

内容的提问来源于stack exchange，提问作者Pavan Kumar Aryasomayajulu