sql sequence

SQL Sequence | SQL Sequence 2 Easy Examples

by

SQL sequence

SQL Sequence is valuable tool to generate unique number for primary key column.
SQL Sequences are used to auto generate value for specified or defined column.
Sequence in SQL server is used to generate values automatically from defined starting number.

In this Tutorial we will see what Sequence is, how to create sequence, how to fetch sequence and how to use SQL sequences with example.

What is SQL Sequence?

A Squence is database object that can generate sequence of integer values depending on defined logic.

The Sequence values are mostly used to auto generate number which are defined as primary key or unique identifier.
Sequences are not only used for specific tables, but we can also use same sequence to use another table within a database.
Sequences are works like auto incremental fields but in sequences we can define start value to increment and end value to stop increment, we can increase value in sequences as per the sequence definition.

Working of SQL Sequence

Sequences are created using Create Sequence SQL Command and sequences are related to specific database. you cannot use another database’s sequence to generate auto incremental value in table.
you can use Same sequence to generate auto incremental value in any table from the database in which you have created sequence.

After creating Sequence, you need to write SQL statement “NEXT VALUE FOR Your_Sequence_Name” to get the next value from the sequence.
The Value retrieved from the sequence is incremented depending on defined in Sequence. e.g. Increment by 1 etc.
Suppose you have defined in Sequence Increment BY 1 the value will be increased by 1 for example 1,2,3,4 etc. And if you have defined increment by 2 then value will be increased by 2 for example 2,4,6,8 etc.

https://account.microsoft.com/

Create Sequence in SQL

Let’s Create Sequence to generate ID automatic in EmpMaster Table, consider the table EmpMaster with ID column Primary key. and we have to fetch the ID column’s value from the sequence.

CREATE TABLE dbo.EmpMaster
	(
	ID      INT NOT NULL,
	EName   VARCHAR (255) NULL,
	Email   VARCHAR (255) NULL,
	Salary  NVARCHAR (500) NULL,
	DOB     DATE NULL,
	Mobile  NVARCHAR (10) NULL,
	Tel     VARCHAR (10) NULL,
	Address NVARCHAR (500) NULL
	Primary Key (ID))
GO

Sequence Syntax

Create Sequence Sequence_Name
Start WIth <Value>
Increment by <Value>
End with  <value>

Sequence Start With: This keyword is used to define the starting value of sequence, means from which value sequence number generation will start. you can define this Start value when creating the sequence.

Sequence Increment By: Increment by in creating sequence is used to define what value will be increased by

Next Value for Sequence SQL: Next value for Sequence is used to retrieve the unique value from the sequences this will be used while inserting data into tables.

SQL Sequence Example

Create Sequence that start with 1548 and increment by 2


Create Sequence EmpMaster_Seq
Start with 1548
Increment By 2
Max Value 5000
Cycle;

Example How to fetch Sequence value in Table.

How to insert data in SQL Insert


INSERT INTO EmpMaster (ID, EName, Email, Salary, DOB, Mobile, Tel, Address)
VALUES (Next value for EmpMaster_seq, 'lucky munjal', 'lucky@gmail.com', '98542', '2000-09-29', '8798987898', '235689', 'Delhi')
GO

In Above example when we insert new data into the table EmpMaster the ID column will fetch incremental unique value from the sequence ‘EmpMaster_Seq’.

Auto generate Number in SQL

Alter Sequence in SQL

Alter sequence is used to modify the defined parameters in Sequence,

Consider example we have created sequence with start value 1548 and now we want it to start from the 1 then you can alter the sequence to restart value from 1.

Alter Sequence Sequence_name restart with value

Alter Sequence EmpMaster_Seq restart with 1

when we insert first records in EmpMaster Table the ID value will be 1548 and after insert second record in EmpMaster Table the ID value will be 1550 for third record ID value is 1552 it means that ID value is increment by 2 which is defined in our sequence.

Advantages of SQL Sequences

  1. Efficiency
  2. Data Integrity
  3. Flexibility

Data Efficiency

Sequences are high efficient to generate distinct values, Sequences avoid duplicate insertion of data.
By Using SQL Sequence, you can increment value from your given number – you can start your auto-generate number as per your requirement for that you have to define start value while creating sequence.
In SQL Sequence you can restart the sequence to generate but make sure that you do not start the number which was specified earlier.

Data Integrity

Sequence can maintain data integrity, it will maintain each newly generated value is unique and it will generate number in Sequential series.

Flexibility

SQL sequences are not only associated with single table, but sequences are also associated with the database you can use SQL sequence in any table within a database. SQL Sequences are flexible can be used for many tables within same database on which sequences is created.

FAQ on SQL Sequences

Q. What is sequence in SQL?

ANs: SQL Sequence is a database object that is used to generated auto generated unique values in sequencial order

Q. How to create sequence in SQL?

Ans: To create a sequence in sql server use below syntax
and example

Sequence Syntax

Create Sequence Sequence_Name
Start WIth
Increment by
End with

Sequence Example

Create Sequence EmpMaster_Seq
Start with 1548
Increment By 2
Max Value 5000
Cycle;

Q. How to use Sequence to generate value from column?

Ans: You can use below SQL statement to get value from sequence

‘Next value for Your_Sequence_name

Example

INSERT INTO EmpMaster (ID, EName, Email, Salary, DOB, Mobile, Tel, Address)
VALUES (Next value for EmpMaster_seq, ‘lucky munjal’, ‘lucky@gmail.com’, ‘98542’, ‘2000-09-29’, ‘8798987898’, ‘235689’, ‘Delhi’)
GO

Q. How to reset a sequence to a specific value?

Ans : To reset sequence value , you can use Alter Sequence statement in SQL

below is the example of Alter Sequence in SQL Server.

ALTER SEQUENCE EmpMaster_Seq RESTART WITH 1

Q. How to Alter SQL sequence?

Ans: You can change Sequence definition by using Alter Sequence Statement in SQL Server. By Using Alter Sequence Statement you can change start value, increment by min value, max value and end value in SQL Sequence.

https://learn.microsoft.com/en-us/sql/

Q. How to drop sequence in SQL?

Ans: To delete Sequence in SQL use Drop Sequence Statement in SQL Server

Fo example

Drop Sequence Your_Sequence_name

Drop Sequence EmpMaster_Seq