Skip to content
Tutorials Center
My learning

Topics

Lesson 71 of 125 ยท Designing Databases

Sequences and Generating Numbers

Learn SQL sequences: CREATE SEQUENCE options, NEXTVAL and NEXT VALUE FOR across databases, why gaps happen and how to generate number series.

An auto-increment column numbers the rows of one table. Sometimes that is too narrow. You may want one invoice number series shared by three tables, order numbers that start at 10,000 and step by 5, or ids reserved before the row exists so you can write them into several tables in one go. For those jobs databases offer sequences: standalone objects that do nothing but hand out numbers.

This lesson explains how sequences are created and tuned, how each database asks for the next value, why gaps are normal, and how to produce a plain series of numbers (1 to 100, say) when you need one for a report. Sequences are not available in SQLite or MySQL, so the dialect syntax is read-only and the runnable examples use a counter table to demonstrate the same ideas.

What a sequence is

A sequence is a named database object with a current value and a rule for the next one. You don't INSERT into it; you ask it for the next number. Each request consumes a number, no matter what you do with it afterwards. Two concurrent sessions never receive the same value.

SQL
-- PostgreSQL, SQL Server, Oracle (read-only here)
CREATE SEQUENCE invoice_no
  START WITH 1000
  INCREMENT BY 1
  MINVALUE 1000
  MAXVALUE 999999999
  NO CYCLE
  CACHE 20;

The options are nearly the same everywhere, and all are optional:

Option Meaning
START WITH n The first value handed out.
INCREMENT BY n The step; negative for a descending sequence.
MINVALUE / MAXVALUE The bounds. Reaching one raises an error unless the sequence cycles.
CYCLE / NO CYCLE Whether to wrap around to the other bound (Oracle uses NOCYCLE).
CACHE n Pre-allocate n values in memory for speed. Cached values are lost on a restart, which creates gaps.
AS type The integer type (SMALLINT, INT, BIGINT) in PostgreSQL and SQL Server.

A sequence is a schema object, so it needs a name that doesn't clash with tables, and a privilege (USAGE in PostgreSQL, SELECT in Oracle, UPDATE in SQL Server) to use it.

Asking for the next value

The call differs by database, and this is the main thing to remember when you port code:

Database Next value Current value (this session)
PostgreSQL nextval('invoice_no') currval('invoice_no'), lastval()
SQL Server (2012+) NEXT VALUE FOR invoice_no sys.sequences.current_value (not per session)
Oracle invoice_no.NEXTVAL invoice_no.CURRVAL
MariaDB 10.3+ NEXT VALUE FOR invoice_no or nextval(invoice_no) lastval(invoice_no)
MySQL, SQLite no sequences n/a
SQL
-- PostgreSQL
SELECT nextval('invoice_no');    -- 1000
SELECT nextval('invoice_no');    -- 1001
SELECT currval('invoice_no');    -- 1001 for this session

INSERT INTO invoices (number, customer_id) VALUES (nextval('invoice_no'), 42);

-- Oracle
INSERT INTO invoices (number, customer_id) VALUES (invoice_no.NEXTVAL, 42);

-- SQL Server
INSERT INTO invoices (number, customer_id) VALUES (NEXT VALUE FOR invoice_no, 42);

currval only works after you have called nextval in the same session. That's deliberate: it lets you reuse the number you just got in later statements (for example the parent id when inserting its child rows) without worrying that another user advanced the sequence in between.

Using a sequence as a column default

You can let the table call the sequence for you:

SQL
-- PostgreSQL
CREATE TABLE invoices (
  number INTEGER PRIMARY KEY DEFAULT nextval('invoice_no'),
  customer_id INTEGER NOT NULL
);

-- SQL Server
CREATE TABLE invoices (
  number INT PRIMARY KEY DEFAULT (NEXT VALUE FOR invoice_no),
  customer_id INT NOT NULL
);

-- Oracle 12c+
CREATE TABLE invoices (
  number INTEGER DEFAULT invoice_no.NEXTVAL PRIMARY KEY,
  customer_id INTEGER NOT NULL
);

Now plain inserts get numbers automatically, but the sequence stays independent: point several tables at the same one and they share a single numbering. Compared with an identity column (GENERATED ... AS IDENTITY), which is tied to one table and column, a sequence is the flexible tool and the identity column is the simple one. PostgreSQL's serial and identity columns are just sequences created and owned for you.

Changing and inspecting

SQL
ALTER SEQUENCE invoice_no RESTART WITH 5000;        -- PostgreSQL, SQL Server
SELECT setval('invoice_no', 5000);                    -- PostgreSQL: set the current value
SELECT last_value FROM invoice_no;                    -- PostgreSQL
SELECT current_value FROM sys.sequences WHERE name = 'invoice_no';   -- SQL Server
SELECT last_number FROM user_sequences WHERE sequence_name = 'INVOICE_NO';  -- Oracle
DROP SEQUENCE invoice_no;

After bulk-loading rows with explicit ids, you typically have to move the sequence past the largest id, otherwise the next generated id collides with an existing one. In PostgreSQL that is SELECT setval('invoice_no', (SELECT MAX(number) FROM invoices));.

A sequence, live

SQLite has no CREATE SEQUENCE, but a counter table with UPDATE ... RETURNING is a faithful stand-in: the increment and the read happen in a single statement, so two connections can't receive the same value. Here two tables share one numbering, the way a shared sequence would work:

SQL One counter shared by two tables

Press Run (or Ctrl + Enter) to see the result here.

That statement returns 1001 and the counter moves on. To insert with it, SQLite needs two steps, because an INSERT cannot call the update directly: take the number, then use it. In a real database nextval goes straight inside the INSERT. For practice, this version builds all the rows from numbers taken in order, and shows that the two tables never reuse one:

SQL Numbers never repeat across tables

Press Run (or Ctrl + Enter) to see the result here.

The three documents have numbers 1001, 1002 and 1003: one series across two tables, which an auto-increment column per table cannot give you.

Gaps are normal

Real sequences are not transactional. When you call nextval, the increment is permanent immediately, even if your transaction rolls back or the insert fails. Consequently:

  • A rolled-back insert leaves a gap: the numbers 1001 and 1002 exist, 1003 was consumed and lost, the next row is 1004.
  • A server crash can lose cached values.
  • Concurrent sessions interleave, so ids are not in commit order.

This is by design: making sequences transactional would force every session to wait for the previous one to commit. It means that sequence values are unique and increasing, but not gapless. If you need gapless numbers (for tax invoices in some countries, for example), don't use a sequence. Use a counter table updated inside the same transaction as the insert, accept the contention (only one transaction at a time can hold the counter row), and test it with your load.

The counter table in the live example above is gapless for exactly that reason: roll back the transaction and the counter rolls back too. See what happens:

SQL A counter table has no gaps, a sequence would

Press Run (or Ctrl + Enter) to see the result here.

The counter is back at 1000. A true sequence would have stayed at 1001 after the equivalent nextval and ROLLBACK. Which behavior you want decides which tool to use.

Generating a series of numbers

The word "sequence" has a second meaning: a list of consecutive numbers such as 1 to 100, used for calendars, filling gaps in reports, or test data. The tool is different. In PostgreSQL there's a function:

SQL
SELECT n FROM generate_series(1, 10) AS g(n);              -- PostgreSQL
SELECT n FROM generate_series(1, 10, 3) AS g(n);           -- step of 3: 1, 4, 7, 10

SQL Server 2022 added GENERATE_SERIES(1, 10) too. Everywhere else, and in SQLite, the portable answer is a recursive CTE:

SQL A number series with a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

A series becomes useful when joined to data. This query lists every day of a week, including days with no sales, which a plain GROUP BY on the sales table would simply omit:

SQL Filling gaps with a generated series

Press Run (or Ctrl + Enter) to see the result here.

Seven rows come back, with zeros on the days nobody bought anything. The pattern (generate the complete range, then LEFT JOIN the facts) is the standard way to build reports without holes.

Which one should you use?

  • Identity or auto-increment column: one table, one key, minimal fuss. Start here.
  • Sequence: one numbering shared by several tables, custom step or start, ids needed before insert, or Oracle and older PostgreSQL code.
  • Counter table: gapless numbering with transactional semantics, at the price of serializing writers.
  • UUIDs: ids generated by the client, no coordination at all; see the auto-increment lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A transaction calls nextval on a sequence and then rolls back. What happens to the sequence?

    Sequences are not transactional. A value, once handed out, stays consumed, which is why gaps are normal.
  2. 2. How do you ask for the next value in SQL Server?

    SQL Server uses NEXT VALUE FOR. PostgreSQL uses nextval() and Oracle uses seq.NEXTVAL.
  3. 3. Why would you pick a sequence over an auto-increment column?

    A sequence is independent of any table, so several tables can share it and you can fetch values ahead of time. It does not guarantee gapless numbers.
  4. 4. Which technique generates the numbers 1 to 10 in SQLite?

    SQLite has no generate_series by default and no sequences, but a recursive CTE builds the series portably.

Summary

  • A sequence is a table-independent object that hands out unique, increasing numbers; options are START WITH, INCREMENT BY, bounds, CYCLE and CACHE.
  • The call is nextval() in PostgreSQL, NEXT VALUE FOR in SQL Server and .NEXTVAL in Oracle; MySQL and SQLite have no sequences.
  • Sequences are not transactional, so rollbacks, crashes and caching create gaps. Use a counter table if you need gapless numbers.
  • A sequence can be a column default and can be shared by many tables.
  • For a plain list of numbers or dates, use generate_series where it exists and a recursive CTE elsewhere, then LEFT JOIN your data onto it to fill gaps.

Next: Keys, the constraints that make those numbers meaningful.