Difference between sequence and identity in SQL Server

sql server identity vs sequence

In this video we will discuss the difference between SEQUENCE and IDENTITY in SQL Server

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

This is continuation to Part 134. Please watch Part 134 from SQL Server tutorial before proceeding.

Sequence object is similar to the Identity property, in the sense that it generates sequence of numeric values in an ascending order just like the identity property. However there are several differences between the 2 which we will discuss in this video.

Identity property is a table column property meaning it is tied to the table, where as the sequence is a user-defined database object and is not tied to any specific table meaning it's value can be shared by multiple tables.

Example : Identity property tied to the Id column of the Employees table.

CREATE TABLE Employees
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(50),
Gender NVARCHAR(10)
)

Example : Sequence object not tied to any specific table

CREATE SEQUENCE [dbo].[SequenceObject]
AS INT
START WITH 1
INCREMENT BY 1

This means the above sequence object can be used with any table.

Example : Sharing sequence object value with multiple tables.

Step 1 : Create Customers and Users tables

CREATE TABLE Customers
(
Id INT PRIMARY KEY,
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
GO

CREATE TABLE Users
(
Id INT PRIMARY KEY,
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
GO

Step 2 : Insert 2 rows into Customers table and 3 rows into Users table. Notice the same sequence object is generating the ID values for both the tables.

INSERT INTO Customers VALUES (NEXT VALUE for [dbo].[SequenceObject], 'Ben', 'Male')
INSERT INTO Customers VALUES (NEXT VALUE for [dbo].[SequenceObject], 'Sara', 'Female')

INSERT INTO Users VALUES (NEXT VALUE for [dbo].[SequenceObject], 'Tom', 'Male')
INSERT INTO Users VALUES (NEXT VALUE for [dbo].[SequenceObject], 'Pam', 'Female')
INSERT INTO Users VALUES (NEXT VALUE for [dbo].[SequenceObject], 'David', 'Male')
GO

Step 3 : Query the tables
SELECT * FROM Customers
SELECT * FROM Users
GO

Output : Notice the same sequence object has generated the values for ID columns in both the tables

To generate the next identity value, a row has to be inserted into the table, where as with sequence object there is no need to insert a row into the table to generate the next sequence value. You can use NEXT VALUE FOR clause to generate the next sequence value.

Example : Generating Identity value by inserting a row into the table

INSERT INTO Employees VALUES ('Todd', 'Male')

Example : Generating the next sequence value using NEXT VALUE FOR clause.

SELECT NEXT VALUE FOR [dbo].[SequenceObject]

Maximum value for the identity property cannot be specified. The maximum value will be the maximum value of the correspoding column data type. With the sequence object you can use the MAXVALUE option to specify the maximum value. If the MAXVALUE option is not specified for the sequence object, then the maximum value will be the maximum value of it's data type.

Example : Specifying maximum value for the sequence object using the MAXVALUE option

CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 1
MAXVALUE 5

CYCLE option of the Sequence object can be used to specify whether the sequence should restart automatically when the max value (for incrementing sequence object) or min value (for decrementing sequence object) is reached, where as with the Identity property we don't have any such option to automatically restart the identity values.

Example : Specifying the CYCLE option of the Sequence object, so the sequence will restart automatically when the max value is exceeded

CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

[hemantbamane]

Hello sir, i have gone through all the 135 videos and truly this is great job you have done with very neat and very simple explanation .. Heartily thank you so much..👌

[federico.r.figueredo]

I went through all the 135 videos and I must say that this must be one of the highest quality SQL Server video series of all YouTube. Thank you very much Mr. Venkat.

[CrixesBlankz]

Thank you very much, this clear my doubts about this topic!! You have earned a new suscriber!

[krzysztofs]

IS THIS THE END ???? IS THIS REALLY THE END ?
Why Venkat ? WHY ?

Thank U for all this tutorial.
You are master of SQL Server. Thank U for educating community. Have a great life and God bless U. I watched all of 135 films and they are superb!

[Oom_Sakkie]

jesus, his voice sounds so relaxed and soothing.

[srikanthyenugu]

Hello Sir,
Firstly i would like to thank you for your valuable tutorials. It means a lot for us.
I have problem with updating the table rows with foreign key constraints. Can you please upload a video explaining how to update and delete table rows with foreign key constraints.
Thanks again for your patience and tutorials.

[geminism]

I believe we have an option to reset the identity by using the DBCC checkident command and we can also deploy a trigger such that once a limit reaches, the DBCC command reset the Identity to 1.

[nancychikara]

Hello Venkat,
I would really appreciate if you could answer the below questions. These were the questions asked to me during an job interview,

Ques1)How to go about picking a good PARTITION key for a big table, and what are some of the "gotchas" of a badly chosen PARTITION key.

Ques2)Someone complains that their app is running slow because of what they think is a "database problem". How do you go about investigating the slowness and helping them to improve their queries?

[srikanthreddy]

Hi Sir.. Please post a video about Quey Optimization and Performance Tuning.. It would be very helpful for interview.
Thank you in advance.

[mohammadaqeel]

Venkat Thanks a lot..very useful videos.. Thanks once again.. Do you have c++ videos.? and also Data Structure.?

[Gagosmith]

Very helpful vids, many thanks! Could you also upload vids about managing XML data in SQL Server pls?

[shikhanikhara]

Hello Venkat,
Do you have any videos realted to SSIS, SSAS and SSRS.

[mohammedrabea]

Can you provide video session about Backup and resotring databases

[mohamednoordheen]

Hi Venkat.. can u pls upload videos on Cross-Server logic in SQl-Server.

[DailySwitzerlandVlogs]

I have problem in query which is ..., show all the employees that's makes more than their supervisor... what is that mean exactly... help

[sharanchintakindi]

Hello Venkat,
I think You have missed the tutorial of encryption and decryption in tour videos.
Please upload it.
Thanking you

[vru]

Hello Sir,
How can we create id 1 2 3 in sequence in teradata?

[mayankgehlot]

is this the complete database? or still some videos are remaining!

[devendrajadhav]

How to increase the value of sequence while generating script of database tables with data? Pls help..

[maheshbhosale]

I need to learn, OAuth2 authentication