Every stored row carries a per-row header, and the engine addresses rows internally by a physical identifier such as page number plus slot number. What lives in that row header, and why should application code never treat the physical row identifier as a stable key?
answer
- header: version info, NULL bitmap, flags
- NULL costs a bit, not a column
- address equals page plus slot, not identity
- updates and reorg move rows; slots get recycled
- fine within one statement, never as a stored key
basics
~20 sThe row header holds bookkeeping the engine needs: version or visibility information, a NULL bitmap with one bit per column, flags such as whether a value is stored off-page, and often a column count. The physical identifier is just the row's current address; updates, page compaction and table reorganisation move rows, so it changes and can even be reused later by a different row.
solid answer
~60 s**Row header.** Ahead of the column data sits per-row metadata: identifiers of the transactions that created and deleted this version (in a multi-version engine), a **NULL bitmap** with one bit per column so NULLs cost no storage in the data area, flags (has off-page values, has been updated, column count), and sometimes a link to a newer version. Only then come fixed-width columns, then variable-length ones with their own length prefixes. **Physical row identifier.** (page, slot) is an address, not an identity. It changes when: - an update stores the new row version on another page, - the table is reorganised, rebuilt or restored, - rows are relocated because a row no longer fits its page. And once every index entry referring to a dead slot is gone, the slot number itself can be reused by an unrelated row. So storing the identifier in an application table, an external cache or a URL yields a reference that silently starts pointing somewhere else. It is legitimate only within a single statement or a short transaction, for example as a self-join handle to distinguish duplicate rows. Identity belongs to a declared primary key.
code
sql · 6 linesDELETE FROM staging_rows a
WHERE a.physical_row_id > (
SELECT MIN(b.physical_row_id)
FROM staging_rows b
WHERE b.col1 = a.col1 AND b.col2 = a.col2
);go deeper
List what the header holds (version information, NULL bitmap, flags) and say the physical identifier is just the row's current location, so it must never be used as a key.
Explain the concrete reasons it moves (updates, reorganisation, restore) and that slot numbers get recycled, so a stale identifier can point to a different row rather than to nothing.
Add the legitimate short-lived uses, the per-row overhead consequences for narrow tables, and how the header enables snapshot visibility checks without extra lookups.
Position it as an identity-versus-address boundary: physical placement is the storage layer's to change, so anything durable or externally visible must key on a declared value the engine guarantees.
## What is in front of every row A stored row is not just its column values. Ahead of them the engine writes a small header, usually on the order of 20 bytes, containing everything needed to interpret and govern the row without consulting anything else: - **Visibility or version information.** In a multi-version engine, the identifier of the transaction that created this version and, once deleted or superseded, the transaction that removed it. A reader compares these against its snapshot to decide whether the row is visible to it. In engines that keep old versions elsewhere, the header instead holds a pointer or a rollback-segment address for reconstructing earlier versions. - **NULL bitmap.** One bit per column, present when any column is nullable. A NULL therefore consumes a bit, not a full column slot, which is why nullable columns are cheap when usually empty. It also means the reader must consult the bitmap before walking the data area, since NULL columns occupy no space there. - **Flags and counts.** Number of columns present (which lets an added column with a default be interpreted without rewriting old rows in some engines), whether any value is stored off-page, whether the row has been updated, whether it is a redirect. - **Length or offset to the data area.** After the header come the fixed-width columns, typically aligned to word boundaries, then variable-length columns each with a length prefix. Alignment padding is why a table with poorly ordered columns can be measurably larger than the same columns arranged widest-first, in engines that lay rows out in declaration order. The practical consequence of the header is that a row's on-disk footprint is meaningfully larger than the sum of its column widths. For narrow tables of a few small columns, the per-row overhead can rival or exceed the payload, which is why very narrow, very tall tables are less space-efficient than newcomers expect. ## The physical row identifier Internally, a row is addressed as (page number, slot number). Different engines expose this under different names; the concept is the same: it is the row's current physical location, and indexes store it to point back at the heap. It is deliberately **not** an identity. Concretely: - **An update relocates the row.** In a multi-version engine, an update writes a new version, often on a different page when the old page has no room, and the new version has a new address. Even in an update-in-place engine, growing a row beyond its page's free space forces a move. - **Maintenance relocates rows.** Rebuilding a table, clustering it by an index, restoring from a logical dump or moving it between tablespaces reassigns every address. - **Addresses are recycled.** After the row is gone and every index entry referencing that slot has been cleaned up, the slot number becomes available again and a completely unrelated row can occupy it. A saved identifier does not merely go stale; it can silently resolve to different data, which is the dangerous failure mode because nothing errors. ## Legitimate uses Within a single statement or a short transaction, the identifier is a useful handle: - deduplicating exactly-identical rows in a table that lacks a key, by keeping the row with the minimum identifier per group, - re-finding a row just read, to update it without re-evaluating an expensive predicate, - diagnosing physical layout questions, such as how rows are spread across pages. In all of these it is scoped to a moment where the engine's own locking or snapshot keeps it meaningful. ## What to use instead A declared primary key: a surrogate key from a sequence or identity column, or a genuine natural key. Application-visible identity must be a value the database guarantees, not a byte address that the storage layer is free to change while nobody is looking. This is the same reason APIs should not expose physical addresses in URLs or caches: correctness must not depend on physical placement. ## Interview framing Say it in one line: the header carries version, NULL and flag information that makes each row self-describing, and the physical identifier is an address whose whole purpose is to be reassignable. Then give the dedup use case to show you know where it is fine, and the recycled-address failure to show why it is not a key.
- Give a concrete failure caused by storing a physical row identifier in an application table.A reporting table saves the identifier of each source row so it can link back. Later the source rows are updated, moving them to new pages, and a table rebuild reassigns addresses wholesale. The saved identifiers now point at whatever rows happen to occupy those addresses, so the report silently joins to unrelated data. Nothing throws an error, which is why the bug survives for months.
- Why does a NULL column cost almost nothing to store?Because nullability is recorded in a bitmap in the row header, one bit per column, and a NULL column occupies no bytes at all in the data area. The reader consults the bitmap to know which columns are present before walking the layout. That is also why adding a mostly-NULL column to a wide table is cheap in space, though it still costs a bit per row in every row's header.
saying these in an interview costs you the question
- Treating the physical row identifier as a permanent surrogate key
- Assuming a row's address never changes as long as the row is not deleted
- Believing NULL values consume the full width of their column
- Thinking the on-disk size of a row equals the sum of its column sizes