HomeОбразованиеRelated VideosMore From: kudvenkat

Difference between sequence and identity in SQL Server

124 ratings | 33343 views
sql server identity vs sequence In this video we will discuss the difference between SEQUENCE and IDENTITY in SQL Server 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 http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-sequence-and.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-sequence-and_27.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Html code for embedding videos on your blog
Text Comments (25)
Courage Osaro (2 months ago)
This is just Awesome. Thank you Sir.
CrixesBlankz (8 months ago)
Thank you very much, this clear my doubts about this topic!! You have earned a new suscriber!
Krzysztof S (1 year ago)
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!
Mayank Gehlot (2 years ago)
is this the complete database? or still some videos are remaining!
Mohamed Noordheen (2 years ago)
Hi Venkat.. can u pls upload videos on Cross-Server logic in SQl-Server.
Hemant Bamane (2 years ago)
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..👌
Yogesh Kumbhar (2 years ago)
I have problem in query which is ..., show all the employees that's makes more than their supervisor... what is that mean exactly... help
Justin Li (2 years ago)
as great as always
Gagosmith (2 years ago)
Very helpful vids, many thanks! Could you also upload vids about managing XML data in SQL Server pls?
dcasta294 (2 years ago)
Thanks a lot!
Mohammad Aqeel (2 years ago)
Venkat Thanks a lot..very useful videos.. Thanks once again.. Do you have c++ videos.? and also Data Structure.?
Creator (2 years ago)
Thanks a Lot. Basically I am an a Electrical & Instrumentation Engineer i was panic to learn any software. but after watching u r videos i got confidence that i can switch my job in to software. thanks a lot. ;) can i have u r personal contact no.
Sharan Chintakindi (2 years ago)
Hello Venkat, I think You have missed the tutorial of encryption and decryption in tour videos. Please upload it. Thanking you
archrodney (1 year ago)
-- Here's an example. Let's say you have a table that contains the details of credit cards -- and you want to encrypt the credit card numbers. -- Let's create the table: create table [CreditCard] ( ID int primary key identity(1,1), CardNumber nvarchar(16) ) -- Let's add some data to the table: insert into [CreditCard] values ('0123456789012345') insert into [CreditCard] values ('1111222233334444') -- Let's add a new column to that table: alter table [CreditCard] add CardNumber_Encrypted varbinary(255) -- Let's encrypt the numbers by passphrase: update [CreditCard] set CardNumber_Encrypted = ENCRYPTBYPASSPHRASE('password', CardNumber) -- Let's check the table: select CardNumber, CardNumber_Encrypted from [CreditCard] -- Now, you can only decrypt the card numbers if you supply your passphrase: select CardNumber, CardNumber_Encrypted, convert(nvarchar(16), DECRYPTBYPASSPHRASE('password', CardNumber_Encrypted)) as CardNumber_Decrypted from [CreditCard] -- There are a few other built-in functions for encrypting data, you can find articles about them on the MSDN: -- https://msdn.microsoft.com/en-us/library/bb510663.aspx
Nancy Chikara (2 years ago)
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?
kudvenkat (2 years ago)
+Nancy Chikara Sure I will try to record and upload as soon as I can, Thank You for your patience. Dot Net & SQL Server training videos for aspiring web developers https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd Download our training videos and slides for offline viewing http://www.pragimtech.com/Order.aspx Code Samples, Text Version of the videos & PPTS on my blog http://csharp-video-tutorials.blogspot.com Tips to best utilise our channel https://www.youtube.com/watch?v=y780MwhY70s To receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below http://www.youtube.com/subscription_center?add_user=kudvenkat Please click the THUMBS UP button below the video, if you think you liked them Thank you for sharing the link with your friends who you think would also benefit from them Best Venkat
Srikanth Yenugu (3 years ago)
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.
shikha nikhara (3 years ago)
Hello Venkat, Do you have any videos realted to SSIS , SSAS and SSRS.
Mohammed Rabea (3 years ago)
Can you provide video session about Backup and resotring databases
Mahesh Bhosale (3 years ago)
I need to learn, OAuth2 authentication
Srikanth Reddy (3 years ago)
Hi Sir.. Please post a video about Quey Optimization and Performance Tuning.. It would be very helpful for interview. Thank you in advance.
kudvenkat (3 years ago)
+srikanth reddy Thanks a million for taking time to give feedback. I am glad you found the videos useful. We don't have videos on the concepts you mentioned. I will record and upload as soon as I can. Thank you for your patience. Free Dot Net & SQL Server videos for web developers https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs for offline viewing, you can order them using the link below http://www.pragimtech.com/Order.aspx Code Samples, Text Version of the videos & PPTS on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use our channel https://www.youtube.com/watch?v=y780MwhY70s Want to receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below http://www.youtube.com/subscription_center?add_user=kudvenkat Please click the THUMBS UP button below the video, if you think you liked them Thank you for sharing these links with your friends Best Venkat
Muhammad Rehbar Sheikh (3 years ago)
Thanks sir!
Riday Kumar (3 years ago)
thank you so much, sir, you guided me to for a programme. I have a simple request that pls make a simple chat app using WCF .or if there is exist in your channel then pls guide me. thank you
raqibul1000000 Alam (3 years ago)
Thanks a lot. 1000 Like.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.