Sql difference between Identity Vs Sequence

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: Start with: Initial value to start with sequence. Increment by: Step by which the values will get incremented or decremented. Minvalue: Minimum value of the sequence. Maxvalue: Maximum value of the sequence. Cycle / No Cycle: To recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number). Cache / No Cache: To pre-allocate the number of sequences specified by the given value.	Syntax: IDENTITY [ (seed , increment) ] Where: Seed: Initial value to start with Increment: Step by which the values will get incremented or decremented each time.
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: Msg 11728, Level 16, State 1, Line 2 The sequence object ‘SequenceMax’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.	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.