When would you use CREATE SEQUENCE and NEXT VALUE FOR instead of an identity column?

## Two shapes of the same generator An identity column and a sequence object are the same machinery exposed differently. Identity binds the generator to one column of one table; you cannot name it in a query, and you get a value only as a side effect of inserting. `CREATE SEQUENCE` promotes the generator to a first-class schema object with a name you can use anywhere a value expression is allowed. ```sql CREATE SEQUENCE order_seq AS BIGINT START WITH 1000 INCREMENT BY 1 MINVALUE 1000 NO MAXVALUE NO CYCLE; ``` The options are the same set an identity column takes in parentheses, which is the clearest sign the two features are one mechanism. ## Drawing a value The ANSI expression is `NEXT VALUE FOR <sequence name>`: ```sql INSERT INTO orders (order_id, customer_id, total) VALUES (NEXT VALUE FOR order_seq, 42, 99.50); ``` Spellings diverge sharply here, and this is the one place portability really bites. PostgreSQL uses a function, `nextval('order_seq')`; Oracle uses `order_seq.NEXTVAL`; SQL Server uses the ANSI `NEXT VALUE FOR`. MySQL has no sequence objects at all, so a schema depending on them does not port there without redesign. Re-reading the value your session last generated is likewise engine-specific, so avoid building on it if portability matters. ## Case 1 — you need the key before the row The classic driver. An order and its lines are written in one transaction, and the lines need the order's id: ```sql -- pseudocode around plain SQL SELECT NEXT VALUE FOR order_seq; -- say it returns 1007 INSERT INTO orders (order_id, ...) VALUES (1007, ...); INSERT INTO order_lines (order_id, ...) VALUES (1007, ...), (1007, ...); ``` With an identity column the id does not exist until the parent INSERT has run, so the application must read it back before it can write the children. Taking the number up front lets you build the whole object graph — or hand the identifier to a caller, or stamp it into a message — before touching the table. It also composes cleanly with multi-row inserts where you want all children to carry a value you already hold. ## Case 2 — one numbering across several tables A business rule such as "every financial document has a unique document number, whether it is an invoice or a credit note" cannot be expressed with two identity columns, which count independently and will collide. One sequence, referenced from both tables, gives a single stream of numbers: ```sql CREATE TABLE invoices (doc_no BIGINT DEFAULT (NEXT VALUE FOR doc_seq) PRIMARY KEY, ...); CREATE TABLE credit_notes (doc_no BIGINT DEFAULT (NEXT VALUE FOR doc_seq) PRIMARY KEY, ...); ``` (Engines differ on whether a sequence draw is permitted in a `DEFAULT` clause and on the exact spelling — PostgreSQL's legacy `serial` type is precisely a column whose `DEFAULT` calls `nextval` on a sequence it created for you.) ## Case 3 — the value is not a key at all Batch numbers, run ids, correlation ids stamped onto a set of rows, a per-import job identifier written into every loaded row: none of these is "the identity of one row in one table", and all of them want a named generator you can draw from wherever you like. ## What you give up The binding disappears. With identity, the generator is created, owned and dropped with the column, and the engine will not let you leave it dangling. With a sequence you have three independent objects — the sequence, the table, and whatever draws from it — and keeping them coherent is your job: dropping the table leaves the sequence behind, restoring one environment from another can restore a table without its sequence at the right position, and nothing stops a statement inserting a value that never came from the sequence at all. Uniqueness is likewise not the sequence's promise. A sequence hands out distinct values as long as it is not cycled or reset; the guarantee that the *column* holds no duplicates still comes from the `PRIMARY KEY` or `UNIQUE` constraint. Declaring `NO CYCLE` matters for exactly this reason — a cycled sequence eventually re-offers numbers that are already stored. ## The default choice For a plain surrogate key, prefer the identity column: fewer objects, an explicit binding, and the write authority controls (`ALWAYS` / `BY DEFAULT`) come with it. Reach for `CREATE SEQUENCE` when one of the cases above actually applies — most often the pre-allocated key or the shared numbering. "Because I might need it later" is not one of them.