The best way to include sequence in your SQL Query

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.

Sequence in SQL
Image courtesy|Stockphotos.io

The syntax is as follows…

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;
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.