Skip to content
Quantoros

What is a sequence and when would you use one?

SQLLevel: IntermediateDatabase Design

What is a sequence and when would you use one?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

A sequence is a database object that generates a series of unique numbers. It's used for auto-incrementing primary keys.

-- In PostgreSQL, SERIAL is syntactic sugar for a sequence:
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY
);

-- Or explicitly:
CREATE SEQUENCE order_number_seq START 1000 INCREMENT 1;
SELECT nextval('order_number_seq');

Sequences are non-transactional — if a transaction is rolled back, the sequence value is NOT rolled back. You may have gaps in your IDs. If gapless sequences matter for your use case, you need a different approach (though gapless sequences are almost always a bad requirement).

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions