SQL Query, Here’s Best Example to Add Sequence Number

What is sequence in SQL? Typically, a sequence is used to generate a value for primary key of a table. The CREATE SEQUENCE statement followed by the sequence name.

The sequence always starts with ‘1’ and incremented by ‘1’. The examples given here tested for Oracle. You can try in other databases also.

Note-You can also control starting number of sequence and increment by quantity very well.

Syntax for Sequence

The below are the best examples for sequence.

CREATE SEQUENCE SEQUENCE_NAME
[START WITH STARTING_INTEGER]
[INCREMENT BY INCREMENT_INTEGER]
[{MAXVALUE MAXIMUM_INTEGER | NOMAXVALUE}]
[{MINVALUE MINIMUM_INTEGER | NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE CACHE SIZE |NOCACHE}]
[{ORDER|NOORDER}]

CREATE SEQUENCE VENDOR_ID_SEQ;

CREATE SEQUENCE VENDOR_ID_SEQ
START WITH 124;

CREATE SEQUENCE TEST_SEQ
START WITH 100 INCREMENT BY 10
MINVALUE 0 MAXVALUE 1000000
CYCLE CACHE 10 ORDER;

The default value for sequence starts with ‘1’ and increment by ‘1’. There are pseudo columns in NEXTVAL and CURRVAL to get the next and current values.

SELECT VENDOR_ID_SEQ.CURRVAL from dual;
SELECT VENDOR_ID_SEQ.NEXTVAL from dual;