SQL SEQUENCES

Last Updated : 30 Jan, 2026

SQL sequences are used to generate unique numbers automatically for things like primary keys and IDs. They help keep data organized and consistent in a database.

  • They generate unique values.
  • They are used for IDs and keys.
  • They can be customized using START, INCREMENT, MIN, and MAX.
  • Sequence values are not reused, even after rollback.

Features of SQL Sequences

The functionality of SQL sequences is defined by the following features:

  • Automatic Primary Keys: Sequences generate unique values for primary or unique keys.
  • Order Control: They can produce numbers in ascending or descending order.
  • Reusable: One sequence can be used for multiple tables.
  • Independent: Sequences are not linked to any specific table.
  • Efficient: They save time and reduce the need for manual ID generation.

SQL Sequences Working

When creating a sequence, we set the start value, increment, and limits. It can also restart (cycle) when it reaches the maximum.

Syntax:

CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE min_value
MAXVALUE max_value
CYCLE | NOCYCLE;

Example 1: Creating a Sequence in Ascending Order

CREATE SEQUENCE sequence_1
start with 1
increment by 1
minvalue 0
maxvalue 100
cycle;
  • The query creates a sequence named sequence_1.
  • It starts from 1 and increases by 1 up to 100.
  • After reaching 100, it restarts from 1.

Example 2: Creating a Sequence in Descending Order

CREATE SEQUENCE sequence_2
start with 100
increment by -1
min value 1
max value 100
cycle;
  • The query creates a sequence named sequence_2.
  • It starts from 100 and decreases by 1 each time.
  • The values stay between 1 and 100.

Using SQL Sequences in Database Operations

Once a sequence is created, it can be used across multiple tables to generate unique values, such as primary key identifiers or serial numbers. This allows for consistent, efficient value generation, reducing the need for manual input and ensuring uniqueness across different rows and tables.

Example: Using a Sequence to Insert Values

Let's create a students table and use the sequence to automatically generate unique student IDs. In this example, the NEXTVAL function is used to retrieve the next value in the sequence (sequence_1) and insert it into the ID column for each student.

Query:

CREATE TABLE students
( 
ID number(10),
NAME char(20)
);

INSERT into students VALUES
(sequence_1.nextval,'Shubham');
INSERT into students VALUES
(sequence_1.nextval,'Aman');

Output

Screenshot-2026-01-29-122653

Cache Management in SQL Sequences

SQL Server uses CACHE to store sequence numbers in memory, which reduces disk access and improves performance when generating new values.

Example : If a sequence starts at 1 with CACHE 15, SQL Server stores values 1 to 15 in memory. After that, it stores 16 to 30. This makes number generation faster. If the server restarts, some unused numbers may be skipped, which is normal. To avoid gaps, we can use NOCACHE, but it will be slower because it reads from disk every time.

Creating a Sequence with Cache

CREATE SEQUENCE sequence_3
START WITH 1
INCREMENT BY 1
CACHE 10;

Practical Use Cases for SQL Sequences

SQL sequences are used to generate unique and ordered numbers in a database for different purposes.

  • Primary Key Generation: Sequences are used to create unique IDs for each record in a table, especially when many records are added.
  • Serial Numbers and Order IDs: They are used to generate order numbers and product serial numbers in systems like e-commerce.
  • Auditing and Tracking: Sequences help create unique reference numbers for tracking transactions and system activities.
Comment

Explore