Below table lists out the major difference between SEQUENCE and IDENTITY in Sql Server
[ALSO READ] Introduction to Sequence
| SEQUENCE | IDENTITY |
| Sequence object is introduced in Sql Server 2012 | Identity Column property is introduced in Sql Server 6.0 |
| Sequence is a user-defined database object and as name suggests it generates sequence of numeric values according to the properties with which it is created | Identity property is a table column property. It is also used to generate a sequence of numbers according to the properties with which it is created |
| Sequence object can be shared across multiple tables | Identity property is tied to a Table |
| Sequence object can be used to generate database-wide sequential number across multiple tables. | Identity property can be used to generate a sequence numbers at a table level |
| A sequence is created independently of the tables by using the CREATE SEQUENCE statement | Identity property can be specified for a table column in CREATE TABLE or ALTER TABLE statement |
Syntax:
CREATE SEQUENCE
[schema_name.]sequence_name
[ AS [built_in_integer_type
| user-defined_integer_type]]
[ START WITH <constant>]
[ INCREMENT BY <constant>]
[ { MINVALUE [<constant>] }
| { NO MINVALUE } ]
[ { MAXVALUE [<constant>] }
| { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [<constant>] }
| { NO CACHE } ]
[ ; ]
Where: |
Syntax:
IDENTITY [ (seed , increment) ] Where: |
Below is simple example of creating a sequence with Initial value 1 and Increment by 1
CREATE SEQUENCE [DBO].[SequenceExample] AS INT START WITH 1 INCREMENT BY 1 GO |
Below is an example of creating a customer table with identity column Id with initial value as 1 and increment by 1
CREATE TABLE dbo.Customer ( Id INT IDENTITY(1,1), Name NVARCHAR(50) ) GO |
| We can get the next sequence value by using NEXT VALUE FOR function without needing to insert a record to the table Example: Getting Next Sequence Value in A SELECT Statement without inserting a record SELECT (NEXT VALUE FOR DBO.SequenceExample) AS SeqValue RESULT: |
Only way to generate the next identity value is by inserting a record to the table in which the identity column is defined. Example: Insert a record to get the next identity value INSERT INTO dbo.Customer (Name)
VALUES('Basavaraj Biradar')
GO
SELECT SCOPE_IDENTITY()
GO
SELECT * FROM dbo.Customer
RESULT: |
We can use a script like below to get the sequence object’s current value:
SELECT Current_Value FROM Sys.Sequences WHERE name='SequenceExample'
|
We can use a script like below to get the identity columns current value (i.e. last identity value generated as a result of insert):
SELECT IDENT_CURRENT('Customer')
AS 'Identity Current value'
|
Sequence object provides an option to reset the current sequence value as-well as the increment step size
ALTER SEQUENCE dbo.SequenceExample RESTART WITH 20 INCREMENT BY 2 GO
|
Column identity property current value can be reseeded but not it’s increment step size Example: The following script resets the Customer tables current identity value to 20.
DBCC
CHECKIDENT('Customer', RESEED,20)
|
| Sequence object provides an option to define the maximum sequence value. If it is not specified, by default it takes the maximum value of the Sequence object data type.
Example: Below script creates a sequence object with maximum value as 2 CREATE SEQUENCE [dbo].[SequenceMax] AS INT START WITH 1 INCREMENT BY 1 MAXVALUE 2 GO Once the Sequence maximum value is reached the request for the next sequence value results in the following error message: |
Identity column property doesn’t provide an option to define the maximum identity value, it always to takes maximum value corresponding to the identity columns data type |
|
Sequence object provides an option of automatic restarting of the Sequence values.If during Sequence object creation the CYCLE option is specified, then once the sequence object reaches maximum/minimum value it will restarts from the specified minimum/maximum value.
Example: Create a sequence object with CYCLE option which starts with 1 and when Sequence max value 2 is reached it will restart with minimum value 1. CREATE SEQUENCE [dbo].[SeqCycle] AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2 CYCLE GO Let us check how the Sequence values are recycled by calling the NEXT VALUE FOR function for the 3 times as below: SELECT (NEXT VALUE FOR dbo.SeqCycle) AS SeqValue GO 3 RESULT: |
Identity property doesn’t provides an option for the automatic restarting of the identity values |
| Sequence object provides sp_sequence_get_range to get multiple sequence numbers at once. | Identity column property doesn’t provide an option to get multiple values. |
| Sequence object provides an option to enable caching, which greatly increases the performance by reducing the disk IO required to generate the next sequence value. CACHE property allows to define cache size, by default caching is disabled.
To Sequence CACHE management and internals you may like to go through the article: Sequence Cache management and Internals |
Identity property doesn’t provide an option to enable/diable the cache management and also to define the cache size. |
[ALSO READ] Sequence related articles
Introduction to Sequence
Sequence limitations and restrictions
Sequence Cache management and Internals
[ALSO READ] Differences
- Varchar vs NVarchar
- Varchar vs Varchar(MAX)
- Char vs Varchar
- Text vs Varchar(Max)
- Union vs Union All
- DateTime vs DateTime2
- SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
- Stored Procedure vs User Defined Function
- Primary Key vs Unique Key
- RAISERROR vs THROW
- Temporary Table vs Table Variable
- Len() vs Datalength()
- Sequence vs Identity
- DATEDIFF vs DATEDIFF_BIG
- LEFT JOIN vs LEFT OUTER JOIN
- RIGHT JOIN vs RIGHT OUTER JOIN
- JOIN vs INNER JOIN
- LEFT OUTER JOIN vs RIGHT OUTER JOIN
- SMALLDATETIME vs DATETIME
