SQL

Sequences in SQL

🔢 A SEQUENCE is a database object that generates a sequence of unique numeric values. Unlike AUTO_INCREMENT or IDENTITY, a sequence is independent of any specific table and can be shared across multiple tables or applications.

📖 What is a Sequence?

A sequence produces numbers in a defined order whenever its next value is requested. It is commonly used to generate primary key values, invoice numbers, order numbers, and other unique identifiers.

Information

A sequence is a standalone database object. Multiple tables can use the same sequence if required.

🎯 Why Use Sequences?

Sequences provide a flexible way to generate unique numbers without being tied to a single table.

📌 Generate unique identifiers automatically.
📌 Share number generation across multiple tables.
📌 Customize starting values and increments.
📌 Improve flexibility compared to table-specific auto-numbering.
📌 Support enterprise applications requiring centralized numbering.

📝 Basic Syntax

Create a Sequence

CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1;

🔍 Sequence Components

Option	Description
START WITH	Specifies the first generated value.
INCREMENT BY	Determines how much the value increases each time.
MINVALUE	Defines the minimum value.
MAXVALUE	Defines the maximum value.
CYCLE	Restarts from the beginning after reaching the maximum value.
CACHE	Stores generated values in memory to improve performance.

💡 Create a Simple Sequence

Student Sequence

CREATE SEQUENCE StudentSeq
START WITH 1001
INCREMENT BY 1;

The first generated value is 1001, followed by 1002, 1003, and so on.

▶️ Get the Next Sequence Value

The syntax for retrieving the next sequence value varies between database systems.

Oracle

SELECT StudentSeq.NEXTVAL
FROM dual;

PostgreSQL

SELECT nextval('StudentSeq');

SQL Server

SELECT NEXT VALUE FOR StudentSeq;

➕ Use a Sequence During INSERT

Insert Using a Sequence (Oracle Example)

INSERT INTO Students
(StudentID, Name, Department)
VALUES
(StudentSeq.NEXTVAL, 'Alice', 'Computer Science');

Each new row automatically receives the next available sequence value.

⚙️ Create a Custom Sequence

Advanced Sequence

CREATE SEQUENCE InvoiceSeq
START WITH 10000
INCREMENT BY 10
MINVALUE 10000
MAXVALUE 99990
CYCLE;

This sequence begins at 10000, increases by 10, and starts again from the minimum value after reaching the maximum value because of the CYCLE option.

📊 Sample Sequence Output

Request	Generated Value
First	1001
Second	1002
Third	1003
Fourth	1004

⚖️ Sequence vs IDENTITY vs AUTO_INCREMENT

Feature	Sequence	IDENTITY	AUTO_INCREMENT
Independent Object	✅ Yes	❌ No	❌ No
Shared Across Tables	✅ Yes	❌ No	❌ No
Automatic Value Generation	✅ Yes	✅ Yes	✅ Yes
Highly Configurable	✅ Yes	Limited	Limited

💼 Real-World Example

A logistics company uses one centralized sequence to generate shipment IDs across multiple warehouse tables. This guarantees that every shipment number is unique regardless of which table stores the record.

Shipment Sequence

CREATE SEQUENCE ShipmentSeq
START WITH 50000
INCREMENT BY 1;

🗄️ Database Compatibility

Database System	Sequence Support
Oracle	✅ Fully supported.
PostgreSQL	✅ Fully supported.
SQL Server	✅ Supported (SQL Server 2012 and later).
MySQL	Limited native support; AUTO_INCREMENT is commonly used instead.
SQLite	Does not provide standalone sequence objects.

⚠️ Common Mistakes

❌ Assuming sequence values are always gap-free.
❌ Forgetting that requesting the next value advances the sequence.
❌ Using one sequence for unrelated numbering schemes without planning.
❌ Assuming identical syntax across all database systems.

Warning

Sequence numbers may contain gaps because values are typically not reused after rollbacks, failed transactions, or cached allocations, depending on the database system.

⚠️ Best Practices

Best Practice

Use sequences for shared or centralized numbering requirements, choose appropriate starting values and increments, avoid assigning business meaning to generated numbers, understand database-specific syntax, and configure caching and cycling options carefully based on application requirements.

🚀 Key Points to Remember

📌 A sequence is an independent database object.
📌 It generates unique numeric values on demand.
📌 Multiple tables can share the same sequence.
📌 Sequences support configurable starting values, increments, limits, and cycling.
📌 Syntax differs among database systems.
📌 Sequence values are intended to be unique, not necessarily consecutive.

>>"Sequences provide a flexible and centralized way to generate unique numbers across an entire database."

Summary

✅ A SEQUENCE is a powerful SQL object for generating unique numeric values independently of any table. Its flexibility, configurability, and ability to serve multiple tables make it an excellent choice for enterprise applications that require centralized identifier generation.