How does each storage model identify a row over time — a heap with physical row identifiers versus a clustered table keyed on the primary key — and what happens when a row moves or its primary key value is updated?

## Two notions of row identity Every index needs a way to say "the row I mean is *that* one". There are only two families of answer, and the storage model picks one. **Physical identity (heap).** The reference is an address: file number, page number, slot number. Resolving it is trivial — read that page, take that slot. Every index leaf entry stores such an address. **Logical identity (clustered).** The reference is the clustering key value. Resolving it means searching the clustered B+tree for that key. Slower to resolve, but immune to physical movement. The choice is forced by the model. A clustered table constantly relocates rows as leaf pages split, so physical addresses stored in secondary indexes would be invalidated en masse by a single split — every split would require updating every secondary index that referenced the moved rows. Logical references sidestep this entirely. Conversely, a heap does not relocate rows on insert, so a physical address is safe and much cheaper to follow. ## What happens in a heap when a row must move Rows in a heap are stable in position under normal operation, but not unconditionally. The common trigger is an update that grows the row — a variable-length column gets longer — so it no longer fits in the free space of its page. Engines take one of two approaches: - **Forwarding pointer.** Leave a small stub at the original slot pointing at the row's new location. All existing index entries keep working; they resolve to the stub, which redirects. The cost is an extra page access per lookup for that row. Accumulated forwarding is a measurable fragmentation problem, and the standard remedy is a table reorganization or rebuild that relocates rows and repairs references. - **Relocate and repair.** Move the row and update every index entry that pointed at the old address. No lookup penalty afterwards, but the update itself becomes much more expensive, touching every index on the table. Engines also differ in whether an update writes a new row version elsewhere as part of concurrency control; where they do, the same address-stability question arises and is handled with similar machinery. ## What happens in a clustered table Row movement is routine and cheap in terms of references: when a leaf page splits, roughly half its rows move to a new page, and *nothing* needs fixing in the secondary indexes because they never named a physical location. This is precisely the property that makes clustering viable. The price is paid on every secondary read: two descents rather than one descent plus a direct fetch. It is a permanent, per-lookup tax in exchange for zero maintenance on movement. ## Updating the clustering key itself This is the case interviewers probe. In a clustered table the key determines *where the row lives*, so changing it changes the row's address. The engine cannot edit in place; it must: 1. Remove the row from its current leaf page (leaving free space, possibly triggering merge/underfill handling). 2. Insert it at the position implied by the new key, which may split the destination page. 3. Update every secondary index entry, because those entries stored the old key value as the row reference — so all of them must now point at the new one. So a single-column update becomes a delete-plus-insert on the table plus a write to every secondary index. Compare a heap: updating the primary key column there rewrites one column in place and updates only the primary key index; the row does not move and other indexes are untouched. This asymmetry is a strong practical argument for **immutable, meaningless primary keys**. If the primary key is a natural attribute that the business might change — an email address, a business reference code, a document number — it is a poor clustering key, both because it is often wide and because updates to it are expensive and invalidate references everywhere. A narrow surrogate key that never changes gives stable identity, cheap clustering, and lets the mutable natural value live in an ordinary column with a uniqueness constraint. ## Related consequences worth naming - **Application-visible identifiers.** A physical row id is not a durable business identifier; it can change under reorganization, so it should never leak into application data or external references. A clustering key value is a logical identity and is safe to expose — which is another reason it should be immutable. - **Reorganization semantics.** Rebuilding a heap changes row addresses and therefore rewrites every index; rebuilding a clustered table rewrites the table in key order but leaves secondary index *contents* logically unchanged. ## The answer in short "Heaps use physical row ids — cheap to follow, stable on insert, but an update that grows a row forces relocation with a forwarding pointer or index repair. Clustered tables use the clustering key as a logical reference, so rows can move freely on splits with no index maintenance, at the cost of a second descent on every secondary lookup. And updating the clustering key is a delete-plus-insert that also rewrites every secondary index entry — which is why the clustering key should be narrow and immutable."