Heap file versus clustered index

WHAT THIS TESTS The interviewer probes whether you understand that the clustered index is the table, not an add-on, and how that shapes read and write costs.

A GOOD ANSWER COVERS A heap file stores rows in no guaranteed order, wherever free space exists. Inserts are cheap because the engine just appends to a page with room. But there is no ordering, so any indexed read finds a row identifier in a secondary index then performs a separate lookup into the heap, and range scans have no locality. An index-organized table, called a clustered index in SQL Server and effectively how InnoDB stores rows by primary key, physically stores the full rows in primary-key order in the leaf level of a B-Tree. Reads by primary key, and especially key range scans, are fast and effectively covering since the data is right there in order. The cost is on writes: inserting a key in the middle can split a full page, and updates that change the key or grow the row force moves and splits; sequential or appending keys avoid most of this. Secondary indexes in a clustered table store the primary key as the row pointer, so a lookup costs two traversals and a wide key inflates every secondary index.

COMMON WRONG ANSWERS Treating a clustered index as just another secondary index rather than the table storage. Claiming heaps are always slower; they insert faster and suit append-heavy logs. Ignoring page splits from random-key inserts. Forgetting the secondary-index double lookup in clustered tables.

LIKELY FOLLOW-UPS Why do random primary keys hurt clustered tables; why is a narrow clustering key preferred; when is a heap appropriate; what is a forwarding pointer in a heap.

ONE CONCRETE EXAMPLE A table clustered on an auto-increment id appends new rows to the end with no splits and serves WHERE id BETWEEN ranges by reading consecutive pages. Switching the clustering key to a random UUID scatters inserts across the tree, triggering constant page splits and fragmentation, which slows ingestion sharply even though point reads still work.