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

Following is the syntax of AUTO_INCREMENT attribute in MySQL −

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 −

As we can see in the above table, the values in the ID column are auto incremented.

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

Following is the syntax to start the sequence with different value −

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 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 −

As observed in the table above, the values in the “ID” column begin with 100 instead of 1.

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

Following is the syntax to create a sequence in SQL −

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.

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;

In the above query, the sequence is named My_Sequence and it starts with the value 1 and increments by 1 each time a value is generated. The sequence has a maximum value of 5 and cycles back to the starting value when it reaches the maximum value.

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

When you execute the above query, the output is obtained as follows −

(7 rows affected)

Verification

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

SELECT * FROM CUSTOMERS;

The table will be displayed as −