| 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 |