What’s SQL Sequence? How it Improves Performance

In SQL, a sequence is a database object that generates a sequence of unique numeric values. Sequences are typically used to generate primary key values for tables, although they can be used for other purposes as well.

Sequences provide a reliable and efficient way to generate unique identifiers that can be used as primary keys in tables.

Instead of relying on the database to assign a primary key value when a new row is inserted into a table, you can use a sequence to generate a unique identifier in advance, which can be assigned to the new row. This approach can improve performance and reduce the likelihood of primary key collisions.

Syntax for Sequence

Below are the best examples for sequence. The CREATE SEQUENCE statement is followed by the sequence name. We can then use this sequence to generate unique values for a primary key column:

--Syntax:
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}]

--Example-1:
CREATE SEQUENCE VENDOR_ID_SEQ
START WITH 124;

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

The default value for the sequence starts with ‘1’ and increments by ‘1’.

How to use Sequence on DUAL table

Here it shows the usage of the sequence on the DUAL table to get the Current and Next values. Here CURRVAL and NEXTVAL are pseudo columns. Here’s the answer from ASKTOM on the DUAL table.

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

How to use Sequence on my_table

CREATE SEQUENCE my_sequence
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE;

--
CREATE TABLE my_table (
    id INT DEFAULT NEXTVAL('my_sequence'),
    name VARCHAR(50)
);

In this example, we create a table called “my_table” with a primary key column called “id” that uses the “my_sequence” sequence to generate its values. When you insert a new row into this table, the “id” column will automatically be assigned the next value from the “my_sequence” sequence.

Finally, sequences are a helpful tool in SQL for generating unique identifiers and improving database performance.

Related