SQL Sequence Syntax and Use with Examples

SQL Sequence Syntax and Use with Examples

A sequence is an arrangement of whole numbers 1, 2, 3 … that are created all together on demand. Sequences are oftentimes utilized as a part of databases since numerous applications require every row in a table to contain a distinct value, and sequences give a simple approach to create them.

Database systems like MySQL and MS SQL support the sequencing feature to produce unique values. For example: In MySQL, AUTO_INCREMENT is applied on column. This application automatically increments the column value by 1 each time a new record is entered into the table.

Creating a Sequence

Following syntax is used to create sequences:

CREATE Sequence Seq_name

start with initial_val

increment by inc_val

maxvalue max_val

cycle|nocycle

Initial_val is used to specify the starting value of the Sequence i.e. 1.

inc_val – increment value is the value that will be incremented for each record.

max_val – Maximum value is the value specify the max value for the sequence

cycle specifies whether the maximum value exceeds the set limit or not. If it is set, then sequence will restart after reaching the max value and if it is set to nocycle then it will not restart rather it will generate an error.

Example

Following query is the example of sequence:

CREATE Sequence mySeq_1

start with 1

increment by 1

maxvalue 99999

cycle;

Example to use Sequence

The Employees table,

Emp_ID

Employee_Name

1

Tim Robbins

2

Adam Steven

3

Alex Murphy

SQL query for new record would be:

INSERT into Employees value(mySeq_1.nextval,’James McGregor’);

Result table will look like,

Emp_ID

Employee_Name

1

Tim Robbins

2

Adam Steven

3

Alex Murphy

4

James McGregor

The nextval property increments the value in the sequence and will be stored in the new row.