Tag Archives: Identity Vs Sequence in Sql

Difference between Sequence and Identity in Sql Server

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:
Sequence in Sql Server

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:
Sequence in Sql Server 1

We can use a script like below to get the sequence object’s current value:

SELECT Current_Value 
FROM Sys.Sequences 
WHERE name='SequenceExample'

Sequence current value

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'

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

Sequence reseeding example 2

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 reseeding example 1

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:
Sequence Cycle example Sql Server

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