In my last post, I had written about the new feature SEQUENCE in SQL SERVER 2012. But in this post, I would like to write the difference between SEQUENCE and IDENTITY in SQL Server 2012 with examples. I hope this example will make these two concepts more clear.
Given below are the differences along with the sample source code:
S.No |
Identity |
Sequence |
1 |
Dependant on table. |
Independent from table. |
2 |
Identity is a property in a table. Example : CREATE TABLE Table ( [ID] int Identity (1,1), [Product Name] varchar(50) )
|
Sequence is an object. Example : CREATE SEQUENCE [dbo].[Sequence_ID] AS [int] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000 NO CYCLE NO CACHE |
3 |
If you need a new ID from an identity column you need to Example : Insert into [test_Identity] Values (‘SQL Server’) GO SELECT @@IDENTITY AS ‘Identity’ –OR Select SCOPE_IDENTITY() AS ‘Identity’ |
In the sequence, you do not need to insert new ID, you can view the new ID directly. Example : SELECT NEXT VALUE
|
4 |
You cannot perform a cycle in identity column. Meaning, you cannot restart the counter after a
|
In the sequence, you can simply add one property to make it a cycle. Example : ALTER SEQUENCE [dbo].[Sequence_ID] CYCLE; |
5 |
You cannot cache Identity column property. |
Sequence can be easily cached by just setting cache property of Example : ALTER SEQUENCE [dbo].[Sequence_ID] CACHE 3; |
6 |
You cannot remove the identity column from the table directly. |
The sequence is not table dependent so you can easily remove it Example : Create table dbo.[test_Sequence] ( [ID] int, [Product Name] varchar(50) ) GO –First Insert With Sequence object INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (NEXT VALUE FOR [Ticket] , ‘MICROSOFT SQL SERVER 2008’) GO –Second Insert without Sequence INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (2 , ‘MICROSOFT SQL SERVER 2012’) |
7 |
You cannot define the maximum value in identity column it is |
Here you can set up its maximum value.
Example : ALTER SEQUENCE [dbo].[Sequence_ID] MAXVALUE 2000; |
8 |
You can reseed it but cannot change the step size. Example : DBCC CHECKIDENT (test_Identity, RESEED, 4)
|
You can reseed as well as change the step size. Example : ALTER SEQUENCE [dbo].[Sequence_ID] RESTART WITH 7 INCREMENT BY 2; |
9 |
You cannot generate range from identity. |
You can generate a range of sequence |
[…] Reference: https://raresql.com/2012/05/01/difference-between-identity-and-sequence/ […]
Hi Muhammad,
Its a really nice post to view the diff b/w Identity and sequence.
[…] Difference between Identity and Sequence in SQL Server 2012 […]
[…] Difference between Identity and Sequence in SQL Server 2012 […]
[…] of the handy objects when we need to maintain one serial number across the tables. I have written few articles on Sequence object earlier in my […]
[…] Difference between Identity and Sequence in SQL Server 2012 […]
[…] Difference between Identity and Sequence in SQL Server 2012 […]