SQL - Sequences in SQL

A sequence in SQL is a database object used to generate a series of unique numeric values automatically. Sequences are especially useful when you need a reliable way to generate numbers for identifiers such as customer IDs, employee IDs, order numbers, invoice numbers, or transaction IDs.

Instead of manually entering a new number every time a record is inserted, a sequence can generate the next available number automatically. For example, a sequence might generate 1001, 1002, 1003, 1004, and so on.

Why Are SQL Sequences Used?

Sequences are mainly used when an application requires automatically generated numeric values. They are particularly useful for creating unique identifiers.

Consider an employee table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Department VARCHAR(50)
);

If employees are added manually, the EmployeeID must be supplied for every new employee. A sequence can eliminate this manual process by generating the value automatically.

A sequence can generate values such as:

EmployeeID
----------
1001
1002
1003
1004
1005

Each time the sequence is requested, it can provide the next value in the series.

Creating a Sequence

The syntax varies slightly between database systems. In databases such as PostgreSQL, Oracle, and SQL Server, sequences can be created using syntax similar to:

CREATE SEQUENCE EmployeeSeq
START WITH 1001
INCREMENT BY 1;

Here:

EmployeeSeq is the name of the sequence.
START WITH 1001 specifies the first value generated.
INCREMENT BY 1 specifies that the value should increase by one each time.

The sequence will conceptually produce:

Getting the Next Sequence Value

The method for retrieving the next value depends on the database system.

For example, PostgreSQL uses:

SELECT nextval('EmployeeSeq');

The first execution returns:

The next execution returns:

Another execution returns:

The sequence maintains its current position so that subsequent requests receive the next value.

Using a Sequence During Data Insertion

A sequence can be used while inserting records into a table.

For example:

INSERT INTO Employees (EmployeeID, EmployeeName, Department)
VALUES (nextval('EmployeeSeq'), 'Rahul', 'Finance');

Another employee can be inserted using:

INSERT INTO Employees (EmployeeID, EmployeeName, Department)
VALUES (nextval('EmployeeSeq'), 'Priya', 'Marketing');

The resulting data could look like:

EmployeeID | EmployeeName | Department
-----------|--------------|-----------
1001       | Rahul        | Finance
1002       | Priya        | Marketing

The application does not need to manually determine the next employee ID.

Starting a Sequence With a Different Number

A sequence does not have to start at 1. You can choose an appropriate starting value.

For example:

CREATE SEQUENCE OrderSeq
START WITH 5000
INCREMENT BY 1;

The generated values will be:

This can be useful when an organization wants identifiers to begin from a particular number.

Using a Different Increment

The increment can also be changed.

For example:

CREATE SEQUENCE InvoiceSeq
START WITH 100
INCREMENT BY 10;

The generated values will be:

This means the sequence increases by 10 instead of 1.

Descending Sequences

Sequences can also be configured to decrease rather than increase.

For example:

CREATE SEQUENCE NumberSeq
START WITH 1000
INCREMENT BY -1;

The generated values would be:

This demonstrates that sequences are not limited to positive, increasing numbers.

Sequence Limits

A sequence can also have a minimum and maximum value.

For example:

CREATE SEQUENCE ProductSeq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000;

This sequence can generate values from 1 through 10,000, depending on the database system and its configuration.

A sequence can also be configured to cycle when it reaches its limit:

CREATE SEQUENCE NumberSeq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE;

With CYCLE, the sequence can restart from its minimum value after reaching its maximum, subject to the database system's rules.

Care must be taken when using cycling sequences for primary keys because reusing an existing identifier can cause duplicate-key conflicts.

Sequence and Primary Key

A sequence is often used together with a primary key.

For example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

A sequence can provide the values for CustomerID:

CREATE SEQUENCE CustomerSeq
START WITH 1
INCREMENT BY 1;

Then:

INSERT INTO Customers
VALUES (nextval('CustomerSeq'), 'Anita');

The sequence generates the identifier while the primary key ensures that the identifier remains unique within the table.

It is important to understand that a sequence itself does not guarantee that a value is a primary key. The primary-key constraint and the sequence serve different purposes. The sequence generates values, while the primary key enforces uniqueness and identifies records.

Sequence vs Identity or Auto-Increment

Sequences are sometimes confused with identity columns or auto-increment columns.

An identity or auto-increment column is generally tied directly to a particular table column. The database automatically generates a value when a row is inserted.

A sequence is a separate database object. It can potentially be used by multiple statements or tables, depending on the database system.

For example, one sequence could be used to generate invoice numbers independently of the table that stores the invoice information.

The exact features and syntax vary between database systems such as Oracle, PostgreSQL, SQL Server, and others.

Advantages of SQL Sequences

Sequences provide several advantages:

Automatic number generation:
Applications do not need to calculate the next number manually.

Reduced risk of duplicate values:
The database manages the sequence progression, reducing problems caused by manually generated identifiers.

Flexibility:
You can specify the starting value, increment, minimum value, maximum value, and other properties.

Independent database object:
Unlike some auto-increment mechanisms, a sequence can exist independently from a particular table.

Useful for large applications:
Sequences can efficiently generate identifiers for large numbers of records.

Important Limitation

Sequences generally should not be assumed to produce gap-free numbers.

For example, a sequence might generate:

If 1003 is obtained but the corresponding transaction is later cancelled, the next value may still be 1004. The unused value 1003 is not necessarily returned to the sequence.

Therefore, sequences are suitable for generating unique identifiers, but they are generally not suitable when an application requires every number in a legal or business numbering series to be continuous without gaps.

Conclusion

A SQL sequence is a database object designed to generate numeric values automatically and efficiently. It can be configured with a starting value, increment, minimum and maximum limits, and other options. Sequences are commonly used to generate identifiers for customers, employees, orders, invoices, and other database records.

Understanding sequences is important because they provide a flexible mechanism for automatic number generation while remaining separate from constraints such as primary keys. Their exact syntax and behavior can differ between database management systems, so developers should always refer to the documentation of the specific SQL database they are using.