SQL sequence

                                                      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 entered into the table. Sequence is also some what similar to AUTO_INCREMENT but its has some extra features.

Creating Sequence

Syntax to create sequences is,

CREATE Sequence sequence-name
start with initial-value
increment by increment-value
maxvalue maximum-value
cycle|nocycle
initial-value specifies the starting value of the Sequence, increment-value is the value by which sequence will be incremented and max value specifies the maximum value until which sequence will increment itself. cycle specifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the beginning. No cycle specifies that if sequence exceeds max value an error will be thrown.

Example to create Sequence

The sequence query is following

CREATE Sequence seq_1
start with 1
increment by 1
max value 999
cycle ;
Example to use Sequence

The class table,

ID NAME
1 abhi
2 adam
4 alex
The sql query will be,

INSERT into class value(seq_1.nextval,'anu');
Result table will look like,

ID NAME
1 abhi
2 adam
4 alex
1 anu
Once you use next val the sequence will increment even if you don't Insert any record into the table.