DBMS SQL Sequences ~ GNIITHELP

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 maxvalue 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 begining. No cycle specifies that if sequence exceeds maxvalue an error will be thrown.

---

Example to create Sequence

The sequence query is following

CREATE Sequence seq_1
start with 1
increment by 1
maxvalue 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 nextval the sequence will increment even if you don't Insert any record into the table.