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