Introduction
SQL Server 2012 comes with many features, one of which is SEQUENCE. SEQUENCE works similarly to the IDENTITY property. There are many differences between these two. This article describes the differences between the IDENTITY property and a SEQUENCE object.
Identity Vs Sequence
| Identity |
Sequence |
| The Identity property of a column is available from SQL Server 2000. |
Sequence object available from SQL Server 2012. |
|
Identity is a table level object, in other words it is dependent on the table.
This property is set or used with the CREATE TABLE and ALTER TABLE statements |
Sequence is a database-level object so it is independent of tables.
A Sequence object allows us to synchronize a seed value across the multiple tables |
| Cannot be controled by application code. |
Can be controled by application code. |
| We cannot restart the Identity counter after the specified interval. |
Using the CYCLE property, we can restart the counter after a specific interval.
Example
ALTER SEQUENCE TableNextId
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 100000
CYCLE |
| We cannot cache the identity column. |
Using the “CACHE” property we can cache a sequence and improve the performance of SQL Server.
Example
ALTER SEQUENCE TableNextId
CACHE 10
Here we set the cache size to 10. it means the first 10 values (from 1 to 10) made available from memory and the last cached value (10) is written into the system table. |
| We cannot define the maximum value for an Identity column. It is dependent on the data type for the identity column. |
Using the MAXVALUE property we can define a maximum value for the sequence.
Example
ALTER SEQUENCE TableNextId
MAXVALUE 100000 |
| We can reseed an Identity property but we cannot change the step size. |
We can alter the seed well as the Step size of a Sequence object any time. |
| We cannot generate a range for an identity column. |
Using the "sp_sequence_get_range" Stored Procedure, we can generate a range of sequence numbers from the sequence object. |
| If any column is marked as an Identity then we cannot insert data within this column directly. We must first turn off the Identity of the column. |
A Sequence does not depend on the table so we can insert any value in the column. |
| We cannot Get the value of an Identity column before inserting a record. |
We can Get the value of the next Sequence number for a Sequence Object before inserting a record.
SELECT NEXT VALUE FOR DBO.TableNextId |
| We cannot create an Identity property in descending order. |
We can create a Sequence number in descending order using a sequence object.
Example
CREATE SEQUENCE [dbo].[TableNextId]
AS [int]
START WITH 10000
INCREMENT BY -1
MINVALUE 1000
MAXVALUE 10000
CYCLE |
Conclusion
Using a Sequence Object we can generate a sequence number and this Sequence can be shared with multiple tables. It is similar to IDENTITY but has a greater scope. According to the requirements, we can select an Identity or Sequence object.