When is SEQUENCE a Bad Choice in SQL Server

 SEQUENCE is a bad choice when we need gap-free numbering, transactional rollback consistency, strong binding with a table, multi-master replication safety, or security-safe non-predictable identifiers. It may also be unsuitable in low traffic systems or where developers wrongly assume continuous numbering. In such cases IDENTITY, GUID, or domain specific generators are better.

SEQUENCE Does Not Rollback

IDENTITY behaves commit-scoped. SEQUENCE does not roll back.

Let’s see the demo

Creating the sequence

CREATE SEQUENCE OrderSeqdemo START WITH 1 INCREMENT BY 1;

Running this sequence in transaction.

BEGIN TRAN SELECT NEXT VALUE FOR OrderSeqdemo; ROLLBACK;

See there after execution we do not commit. We have rollback. Now we are running the sequence and see the value.

It has not rollback.

Table-Level Tight Coupling

IDENTITY belongs to a table. SEQUENCE is independent.