As we know Sequence is new enhanced feature introduced in SQL Server 2012. SEQUENCE work similarly to an IDENTITY value, but where the IDENTITY value is scoped to a specific column in a specific table, the Sequence Object is scoped to the entire database and controlled by application code. This can allow us to synchronize seed values across multiple tables that reference one another in a parent child relationship. Or, with a little bit of code we can also take control on whether or not the next value is used or saved for the next INSERT should the current transaction be rolled back where the IDENTITY value is lost and creates a gap when an INSERT is rolled backed. Here are some key differences as
Sequence |
Identity |
A SQL Server sequence object generates sequence of numbers just like an identity column in sql tables. But the advantage of sequence numbers is the sequence number object is not limited with single SQL table. | IDENTITY is a table specific. |
The status of the sequence object can be viewed by querying the DMV sys.sequences as shown below. SELECT Name,start_value,minimum_value,maximum_value ,current_value FROM |
System Function @@IDENTITY can use for the last-inserted identity value |
Sequence is an object. Example : CREATE SEQUENCE MySequesnceName AS START INCREMENT MINVALUE 1 MAXVALUE 1000 NO NO |
Identity is a property in a table. Example : CREATE ( ID CUSTNAME )
|
You can obtain the new value before using it in an INSERT statement | You cannot obtain the new value in your application before using it |
In the sequence, you do not need to insert new ID, you can view the new ID directly. Example : SELECT NEXT VALUE FOR MySequesnceName |
If you need a new ID from an identity column you need to Example : Insert into TblIdentityChk Values(‘TEST CUSTOMER’) GO SELECT @@IDENTITY AS ‘Identity’ –OR Select SCOPE_IDENTITY() AS‘Identity’ |
You can add or remove a default constraint defined for a column with an expression that generates a new sequence value (extension to the standard) | You cannot add or remove the property from an existing column |
You can generate new values in an UPDATE statement, let see example as UPDATE TableName SET IDD = Next Values for MySequesnceName |
You cannot generate new values in an UPDATE statement when needed, rather only in INSERT statements |
In the sequence, you can simply add one property to make it a cycle. Example : ALTER SEQUENCE MySequesnceName CYCLE; |
You cannot perform a cycle in identity column. Meaning, you cannot restart the counter after a particular interval. |
The semantics of defining ordering in a multi-row insert are very clear using an OVER clause (extension to the standard), and are even allowed in SELECT INTO statements | The semantics of defining ordering in a multi-row insert are confusing, and in SELECT INTO statements are actually not guaranteed |
Sequence can be easily cached by just setting cache property of sequence. It also improves the performance. Example : ALTER SEQUENCE [dbo].[Sequence_ID] CACHE 3; |
You cannot cache Identity column property. |
The sequence is not table dependent so you can easily remove it –Let Insert With Sequence object INSERT INTO TblBooks([ID],[BookName]) GO -Now Insert Second value without Sequence object INSERT INTO TblBooks([ID],[BookName]) GO |
You cannot remove the identity column from the table directly. |
You can define minimum and maximum values, whether to allow cycling, and a cache size option for performance Example : ALTER SEQUENCE MySequesnceName MAXVALUE 2000; |
You cannot define: minimum and maximum values, whether to allow cycling, and caching options |
You can obtain a whole range of new sequence values in one shot using the stored procedure sp_sequence_get_range (extension to the standard), letting the application assign the individual values for increased performance | You cannot obtain a whole range of new identity values in one shot, letting the application assign the individual values |
You can reseed as well as change the step size. Example : ALTER SEQUENCE MySequesnceName RESTART WITH 7 INCREMENT BY 2; |
You can reseed it but cannot change the step size. Example : DBCC CHECKIDENT (TblIdentityChk,RESEED, 4) |