What is an SQL Sequence?

Sequence is a feature supported by some database systems to produce unique values on demand. Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence.

AUTO_INCREMENT is applied on columns, it automatically increments the column value by 1 each time a new record is inserted into the table.

Sequence is also some what similar to AUTO_INCREMENT but it has some additional features too.

---

Creating a Sequence

Syntax to create a sequence is,

CREATE SEQUENCE sequence-name
    START WITH initial-value
    INCREMENT BY increment-value
    MAXVALUE maximum-value
    CYCLE | NOCYCLE;

• The initial-value specifies the starting value for the Sequence.
• The increment-value is the value by which sequence will be incremented.
• The maximum-value specifies the upper limit or the maximum value upto which sequence will increment itself.
• The keyword CYCLE specifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the begining.
• And, NO CYCLE specifies that if sequence exceeds MAXVALUE value, an error will be thrown.

---

Using Sequence in SQL Query

Let's start by creating a sequence, which will start from 1, increment by 1 with a maximum value of 999.

CREATE SEQUENCE seq_1
START WITH 1
INCREMENT BY 1
MAXVALUE 999
CYCLE;

Now let's use the sequence that we just created above.

Below we have a class table,

ID	NAME
1	abhi
2	adam
4	alex

The SQL query will be,

INSERT INTO class VALUE(seq_1.nextval, 'anu');

Resultset table will look like,

ID	NAME
1	abhi
2	adam
4	alex
1	anu

Once you use nextval the sequence will increment even if you don't Insert any record into the table.

---

---