Sequences in SQL

Sequences in SQL are database objects that generate a sequence of unique integer values. They are frequently used in databases because many applications require that each row in a table must contain unique values and sequences provide an easy way to generate them.

Sequences are a feature of many SQL database management systems, such as Oracle, PostgreSQL, SQL server, and IBM DB2.

MySQL does not support the CREATE SEQUENCE statement to create sequences for table rows or columns. Instead, we can use AUTO_INCREMENT attribute.

Sequences in MySQL

In MySQL, we use the AUTO_INCREMENT attribute to generate unique integer values (sequences) for a column. By default, the sequence starts with an initial value of 1 and increments by 1 for each new row.

Syntax

CREATE TABLE table_name (
    column1 datatype AUTO_INCREMENT,
    column2 datatype,
    column3 datatype,
    ...
    columnN datatype
);
  

Example

In the following example, we are creating a table named CUSTOMERS. In addition to that, we are defining AUTO_INCREMENT on ID column of the table.

CREATE TABLE CUSTOMERS (
    ID INT AUTO_INCREMENT,
    NAME VARCHAR (20) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR (25),
    SALARY DECIMAL (18, 2),
    PRIMARY KEY (ID)
);
  

Here, we are adding some records into the above created table −

INSERT INTO CUSTOMERS VALUES 
(NULL, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(NULL, 'Khilan', 25, 'Delhi', 1500.00),
(NULL, 'Kaushik', 23, 'Kota', 2000.00),
(NULL, 'Chaitali', 25, 'Mumbai', 6500.00),
(NULL, 'Hardik', 27, 'Bhopal', 8500.00),
(NULL, 'Komal', 22, 'Hyderabad', 4500.00),
(NULL, 'Muffy', 24, 'Indore', 10000.00);
  

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Starting a Sequence at a Particular Value in MySQL

By default, MySQL sequences start from 1. To start a sequence with a different value, we use the AUTO_INCREMENT in combination with the ALTER statement.

Syntax

ALTER TABLE table_name AUTO_INCREMENT = value;

In the following query, we are creating a table named BUYERS with AUTO_INCREMENT defined on the ID column.

CREATE TABLE BUYERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here, we are making the sequence start with 100 using the ALTER Statement as shown below −

ALTER TABLE BUYERS AUTO_INCREMENT=100;

Now, we are adding records into the BUYERS table using the INSERT INTO statement −

INSERT INTO BUYERS (NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
100	Ramesh	32	Ahmedabad	2000.00
101	Khilan	25	Delhi	1500.00
102	Kaushik	23	Kota	2000.00
103	Chaitali	25	Mumbai	6500.00
104	Hardik	27	Bhopal	8500.00
105	Komal	22	Hyderabad	4500.00
106	Muffy	24	Indore	10000.00

Sequences in SQL Server

In SQL server, a sequence can be created using the CREATE SEQUENCE statement. The statement specifies the name of the sequence, the starting value, the increment, and other properties of the sequence.

Syntax

CREATE SEQUENCE Sequence_Name
START WITH Initial_Value
INCREMENT BY Increment_Value
MINVALUE Minimum_Value
MAXVALUE Maximum_Value
CYCLE|NOCYCLE;

Here,

• Sequence_Name − This specifies the name of the sequence.
• Initial_Value − This specifies the starting value from where the sequence should start.
• Increment_Value − This specifies the value by which the sequence will increment by itself. This can be valued positively or negatively.
• Minimum_Value − This specifies the minimum value of the sequence.
• Maximum_Value − This specifies the maximum value of the sequence.
• Cycle − When the sequence reaches its Maximum_Value, it starts again from the beginning.
• Nocycle − An exception will be thrown if the sequence exceeds the Maximum_Value.

Example

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

We are inserting some records in the above-created table using INSERT INTO statement as shown in the query below −

INSERT INTO CUSTOMERS VALUES 
(NULL, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(NULL, 'Khilan', 25, 'Delhi', 1500.00),
(NULL, 'Kaushik', 23, 'Kota', 2000.00),
(NULL, 'Chaitali', 25, 'Mumbai', 6500.00),
(NULL, 'Hardik', 27, 'Bhopal', 8500.00),
(NULL, 'Komal', 22, 'Hyderabad', 4500.00),
(NULL, 'Muffy', 24, 'Indore', 10000.00 );

The table is successfully created in the SQL database.

ID	NAME	AGE	ADDRESS	SALARY
NULL	Ramesh	32	Ahmedabad	2000.00
NULL	Khilan	25	Delhi	1500.00
NULL	Kaushik	23	Kota	2000.00
NULL	Chaitali	25	Mumbai	6500.00
NULL	Hardik	27	Bhopal	8500.00
NULL	Komal	22	Hyderabad	4500.00
NULL	Muffy	24	Indore	10000.00

Now, create a sequence using the following query −

CREATE SEQUENCE My_Sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 7
CYCLE;

Once the sequence is created, it can be used to generate unique integer values. Now, let us update the data in the ID column of the CUSTOMERS table using the following query −

UPDATE CUSTOMERS SET ID = NEXT VALUE FOR My_Sequence;

Output: (7 rows affected)

Verification

Let us verify whether the sequence is updated in the ID column of the table or not using the following query −

SELECT * FROM CUSTOMERS;

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00