What does TRUNCATE TABLE do to a table's identity or sequence counter?

## The behaviour An identity column or sequence is a generator that hands out the next value on each insert. It is a separate piece of state from the table's rows, and the two statements treat it very differently. `DELETE` only removes rows. The generator is untouched, in every engine, no matter how many rows you delete: ```sql -- last id issued was 5000 DELETE FROM invoices; INSERT INTO invoices (customer_id) VALUES (7); -- gets 5001 ``` `TRUNCATE TABLE` may restart the generator, and SQL:2008 gives you explicit control: ```sql TRUNCATE TABLE staging_events RESTART IDENTITY; -- next insert starts from the seed TRUNCATE TABLE staging_events CONTINUE IDENTITY; -- generator keeps its position ``` ## Defaults differ, so be explicit This is the part worth memorising precisely, because guessing is expensive: - **PostgreSQL** defaults to `CONTINUE IDENTITY` — a bare `TRUNCATE TABLE t` leaves owned sequences where they were; you must ask for `RESTART IDENTITY`. - **MySQL** resets the table's `AUTO_INCREMENT` counter to its start value on `TRUNCATE`. - **SQL Server** resets the identity column to its seed on `TRUNCATE TABLE`. So the same statement text produces different next-ids on different engines. Where the engine accepts the standard options, write the one you mean instead of relying on the default — the reader of the script learns your intent, and the behaviour stops depending on which database the script lands on. ## Why a restarted counter is dangerous Inside the truncated table, reusing ids is harmless: the table is empty, so no primary-key collision is possible. The damage happens wherever those ids were *also* recorded: - An archive or history table keyed by the same id now contains rows for id 42 that describe a completely different entity from the new id 42. - Events already published to a queue or an analytics warehouse reference ids that will be reissued to unrelated rows. - A cache keyed by id serves the old entity until it expires. - A child table that was itself truncated first, then repopulated, may now point at the wrong parent because both counters restarted independently. None of this raises an error. The database's uniqueness guarantee is scoped to the table, and by the time you reuse a value the old row is gone. That is exactly what makes the bug hard: it looks like a data-correctness mystery in a downstream system rather than a database error. ## Where restarting is the right answer Restarting is genuinely useful when the table is *self-contained and re-creatable*: - test fixtures, where deterministic ids make assertions readable and stable; - staging or scratch tables that are emptied and reloaded on every run, and whose ids never leave the load process; - demo or seed data you want to look identical every time. The test-fixture case is the most common in practice — restarting identity between tests means the first inserted row is always id 1, and tests can assert on ids without depending on execution order. ## Getting the DELETE behaviour when you need it If you need the table emptied *and* the counter preserved on an engine that resets by default, either use `DELETE` (which never touches the generator) or use `CONTINUE IDENTITY` where the engine supports it. Conversely, if you need a reset but must use `DELETE` — say, because you need the operation to roll back — you reset the generator separately with the engine's own facility, which is dialect-specific and outside the portable statement contract. ## Interview framing A strong answer states the contrast (`DELETE` never resets, `TRUNCATE` may), names the standard options `RESTART IDENTITY` / `CONTINUE IDENTITY`, admits that the default is engine-specific rather than pretending it is uniform, and then supplies the operational consequence: id reuse is invisible to the database and only bites in systems that recorded the old ids. That last point is what turns a syntax fact into evidence of production experience.