What is an SQL Sequence?

SQL Sequence is a set of integers 1, 2, 3, …., etc that generates a sequence of numeric values. We use sequences because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them. It is useful if you want to automatically generate primary key values as they are unique integers.

The SQL Sequence is a feature in SQL supported by the Oracle database. MySQL supports the AUTO_INCREMENT field to automatically generate unique numbers. SQLServer uses the IDENTITY keyword and MS Access uses the AUTOINCREMENT keyword. All these fields and keywords are similar but with some additional features.

Sequences in Oracle

We use sequences in Oracle to generate unique integers for a column after creating a sequence. We use the CREATE SEQUENCE statement to create a sequence. Below is the syntax to create a sequence.

Syntax:

    CREATE SEQUENCE sequence_name
    START WITH initial_value
    INCREMENT BY increment_value
    MINVALUE minimum_value
    MAXVALUE maximum_valueCYCLE | NOCYCLE;

Parameters:

sequence_name - It is the name of the sequence.

initial_value - It specifies the starting value for the sequence.

increment_value - It is the value by which the sequence will be incremented.

minimum_value - It is the minimum value of the sequence.

maximum_value - It specifies the upper limit or the maximum value up to which sequence will increment itself.

CYCLE - It specifies that if the maximum value exceeds the set limit, the sequence will restart its cycle from the beginning.

NOCYCLE - It specifies that if the sequence exceeds the MAXVALUE value, an error will be thrown.

The below SQL query creates a sequence seq_1 in ascending order. It will start at 1 and will be increment by 1 with a maximum value of 999. It will repeat itself from the start value after exceeding 999.

    CREATE SEQUENCE seq_1
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 999CYCLE;

Output:

Now, we will use the sequence seq_1 that we have created above. First, create a table named students with columns as id and name as shown below.

    CREATE TABLE students( 
       ID   NUMBER(10),
       NAME VARCHAR2(20));

Now insert values into the table students by using the below query. Note that the below query uses seq_1.nextval. After a sequence is created, you can access its values in SQL statements using NEXTVAL. It increments the sequence and returns the new value. It will increment even if you do not want to insert any record into the table.

    INSERT INTO students VALUE (seq_1.nextval, 'Richard');
    INSERT INTO students VALUE (seq_1.nextval, 'James');

Output:

Once you have created your sequence in the database, you might want to remove it from the database. You can remove a sequence from the database by using the DROP SEQUENCE statement. Use the below syntax to drop a sequence.

    DROP SEQUENCE sequence_name;

 

AUTO_INCREMENT in MySQL

AUTO_INCREMENT field is specified just like a constraint while creating a table in MySQL to generate unique integers. The below SQL query specifies an auto-increment feature on the ID column to generate unique primary keys.

    CREATE TABLE Employees (
        ID     INT          NOT NULL AUTO_INCREMENT,
        Name   VARCHAR(50)  NOT NULL,
        Salary VARCHAR(50),
        Age    INT,
        PRIMARY KEY (ID)
    );

By default, the AUTO_INCREMENT starts from 1 but if you want to start from another value, use the below syntax.

    ALTER TABLE table_name AUTO_INCREMENT = value;

Now if you want to insert a new record into the Employees table, you do not have to specify a value for the ID column. A unique value will be added automatically.

    INSERT INTO Employees (Name, Age) VALUE ('Richard', 23);
    INSERT INTO Employees (Name, Age) VALUE ('James', 23);

The defined data type on the AUTO_INCREMENT should be large enough to accommodate many records. When a row is deleted from a table, its auto-incremented id is not re-used. MySQL continues generating new numbers sequentially.

IDENTITY in SQL Server

SQL Server uses an IDENTITY keyword to create a column with autoincrement feature. The below SQL query specifies an auto-increment feature on the ID column to generate unique primary keys. IDENTITY(1,1) means ID value will start from 1 and will increment by 1. You can provide any value to these arguments.

    CREATE TABLE Employees (
        ID     INT          IDENTITY(1,1), PRIMARY KEY,
        Name   VARCHAR(50)  NOT NULL,
        Salary VARCHAR(50),
        Age    INT
    );

Now if you want to insert a new record into the Employees table, you do not have to specify a value for the ID column. A unique value will be added automatically.

    INSERT INTO Employees (Name, Age) VALUE ('Richard', 23);
    INSERT INTO Employees (Name, Age) VALUE ('James', 23);

AUTOINCREMENT in MS Access

MS Access uses an AUTOINCREMENT keyword to create a column with autoincrement feature. The below SQL query specifies an auto-increment feature on the ID column to generate unique primary keys. AUTOINCREMENT(1,1) means ID value will start from 1 and will increment by 1. You can provide any value to these arguments.

    CREATE TABLE Employees (
        ID     INT          AUTOINCREMENT(1,1), PRIMARY KEY,
        Name   VARCHAR(50)  NOT NULL,
        Salary VARCHAR(50),
        Age    INT
    );

Now if you want to insert a new record into the Employees table, you do not have to specify a value for the ID column. A unique value will be added automatically.

    INSERT INTO Employees (Name, Age) VALUE ('Richard', 23);
    INSERT INTO Employees (Name, Age) VALUE ('James', 23);

Conclusion

In this article, we discussed SQL Sequences with examples. We discussed different keywords to perform the auto-increment feature in different databases. We learned that Oracle uses Sequences, MySQL uses AUTO_INCREMENT, SQL Server uses INDENTITTY and MS Access uses AUTOINCREMENT keyword to automatically generate unique integers for a column.

---

---