SQL Server : SEQUENCE vs IDENTITY

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

What are the differences between SEQUENCE and IDENTITY [SEQUENCE vs IDENTITY]?

	SEQUENCE	IDENTITY
Use	SEQUENCE is an object that can be used to generate database-wide sequential number across multiple tables.  A SEQUENCE is created independently of the tables by using the CREATE SEQUENCE statement.	IDENTITY is a column level property of table that can be used to generate a sequence numbers at a table level. IDENTITY property can be specified for a table column in CREATE TABLE or ALTER TABLE statement.
Version Support	SEQUENCE introduced in SQL Server version 2012.	IDENTITY is available in all the version of SQL Server.
Syntax	CREATE SEQUENCE [schema_name . ] sequence_name  [ AS [ built_in_integer_type | user-defined_integer_type ] ]  [ START WITH  ]  [ INCREMENT BY  ]  [ { MINVALUE [  ] } | { NO MINVALUE } ]  [ { MAXVALUE [  ] } | { NO MAXVALUE } ]  [ CYCLE | { NO CYCLE } ]  [ { CACHE [  ] } | { NO CACHE } ]  [ ; ] NOTE: START WITH – The initial value to start with. INCREMENT BY – The step by which the values will get incremented or decremented each time of retrial. MINVALUE - The minimum value of the sequence. MAXVALUE - The maximum value of the sequence. CYCLE / NO CYCLE – To start the sequence from INITIAL VALUE once it reaches to the MAXVALUE or MINVALUE (if increment by is a negative number). CACHE / NO CACHE – To pre-allocate the number of sequences specified by the given value.	IDENTITY [(seed,increment)] NOTE: SEED – The initial value to start with. INCREMENT – The step by which the values will get incremented or decremented each time of insert.
Example	--Create SequenceData Sequence CREATE SEQUENCE           dbo.SequenceData AS INT           START WITH 1           INCREMENT BY 1 --Retrieve cureent sequence value SELECT CURRENT_VALUE FROM Sys.Sequences WHERE name='SequenceData' --> OUTPUT: 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceData) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceData) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 2 -- Retrieve cureent sequence value SELECT CURRENT_VALUE FROM Sys.Sequences WHERE name='SequenceData' --> OUTPUT: 2	--Create Customer Table CREATE TABLE dbo.Customer (   CustomerID INT IDENTITY (1,1),   CustomerName VARCHAR(50),   [IsActive] [bit] NOT NULL     CONSTRAINT DF_Customer_IsActive DEFAULT (1),     CONSTRAINT [PK_Customer_CustomerID]           PRIMARY KEY CLUSTERED ( CustomerID ASC) ON [PRIMARY] ) ON [PRIMARY] --Insert records INSERT INTO dbo.Customer (CustomerName) VALUES('Govindaraj') --Retrieve current scope identity value SELECT SCOPE_IDENTITY() --> OUTPUT: 1 INSERT INTO dbo.Customer (CustomerName) VALUES('Krishnaraj') --Retrieve current scope identity value SELECT SCOPE_IDENTITY() --> OUTPUT: 2 --Retrieve current identity value SELECT IDENT_CURRENT('Customer') AS 'IDENTITY_CURRENT_VALUE' --> OUTPUT: 2
Behaviors	We can get the next sequence value by using NEXT VALUE FOR function without needing to insert a record to the table. Example: --Create SequenceData Sequence CREATE SEQUENCE           dbo.SequenceData AS INT           START WITH 1           INCREMENT BY 1 --Retrieve cureent sequence value SELECT CURRENT_VALUE FROM Sys.Sequences WHERE name='SequenceData' --> OUTPUT: 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceData) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceData) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 1	Only way to generate the next identity value is by inserting a record to the table in which the identity column is defined. Example: --Create Customer Table CREATE TABLE dbo.Customer (   CustomerID INT IDENTITY (1,1),   CustomerName VARCHAR(50),   [IsActive] [bit] NOT NULL     CONSTRAINT DF_Customer_IsActive DEFAULT (1),     CONSTRAINT [PK_Customer_CustomerID]           PRIMARY KEY CLUSTERED ( CustomerID ASC) ON [PRIMARY] ) ON [PRIMARY] --Insert records INSERT INTO dbo.Customer (CustomerName) VALUES('Govindaraj') --Retrieve current scope identity value SELECT SCOPE_IDENTITY() --> OUTPUT: 1 INSERT INTO dbo.Customer (CustomerName) VALUES('Krishnaraj') --Retrieve current scope identity value SELECT SCOPE_IDENTITY() --> OUTPUT: 2
	We can use a script like below to get the SEQUENCE object’s current value by filtering with the Sequence object. Example:  -- Retrieve cureent sequence value SELECT CURRENT_VALUE FROM Sys.Sequences WHERE name='SequenceData' --> OUTPUT: 2	We can use a script like below to get the IDENTITY columns current value which is the last generated identity column value as a result. Example:  --Retrieve current identity value SELECT IDENT_CURRENT('Customer') AS 'IDENTITY_CURRENT_VALUE' --> OUTPUT: 2
	SEQUENCE object provides an option to restart/reset the current sequence value as-well as the increment step size using ALTER command. Example: -- Retrieve current sequence value before altering SELECT CURRENT_VALUE AS 'Before' FROM Sys.Sequences WHERE name='SequenceData' --> OUTPUT: 2 ALTER SEQUENCE           dbo.SequenceData           RESTART WITH 100           INCREMENT BY 2 -- Retrieve current sequence value after altering SELECT CURRENT_VALUE AS 'After' FROM Sys.Sequences WHERE name='SequenceData' --> OUTPUT: 100 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceData) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 100 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceData) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 102 -- Retrieve current sequence value SELECT CURRENT_VALUE AS 'After' FROM Sys.Sequences WHERE name='SequenceData' --> OUTPUT: 102	Column IDENTITY property current value can be reseeded/restarted using DBCC CHECKIDENT command but not with its increment step size. Example: --Retrieve current identity value before reseeding SELECT IDENT_CURRENT('Customer') AS 'Before' --> OUTPUT: 2 DBCC CHECKIDENT('Customer', RESEED, 100) --Retrieve current identity value after reseeding SELECT IDENT_CURRENT('Customer') AS 'After' --> OUTPUT: 100 --Insert a record INSERT INTO dbo.Customer (CustomerName) VALUES('Ramaraj') --Retrieve current scope identity value SELECT SCOPE_IDENTITY() --> OUTPUT: 101 --Retrieve current identity value SELECT IDENT_CURRENT('Customer') AS 'IDENTITY_CURRENT_VALUE' --> OUTPUT: 101
	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: --Create SequenceDataMax Sequence CREATE SEQUENCE           dbo.SequenceDataMax AS INT           START WITH 1           INCREMENT BY 1           MAXVALUE 2 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceDataMax) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceDataMax) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 2 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceDataMax) AS NEXT_SEQUENCE_VALUE --> OUTPUT: Error Once the Sequence maximum value is reached [MAXVALUE 2] the request for the next sequence value results in the following error message: Msg 11728, Level 16, State 1, Line 2 The sequence object 'SequenceDataMax' 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 column 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 SequenceDataCycle Sequence CREATE SEQUENCE dbo.SequenceDataCycle AS INT           START WITH 1           INCREMENT BY 1           MINVALUE 1           MAXVALUE 2           CYCLE --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceDataCycle) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceDataCycle) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 2 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceDataCycle) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceDataCycle) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 2 SELECT (NEXT VALUE FOR   dbo.SequenceDataCycle) AS NEXT_SEQUENCE_VALUE GO 3 --> OUTPUT: 1 --> OUTPUT: 2 --> OUTPUT: 1	IDENTITY property doesn’t provide an option for the automatic restarting of the identity values.
	SEQUENCE object object provides sp_sequence_get_range to get multiple sequence numbers at once. Example: --Create SequenceDataRange Sequence CREATE SEQUENCE dbo.SequenceDataRange AS INT           START WITH 10           INCREMENT BY 1           MINVALUE 1 --Retrieve the next sequence value using range with 5 EXEC sp_sequence_get_range SequenceDataRange, 5, 1, 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SequenceDataRange) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 15	IDENTITY column property doesn’t provide an option to get multiple values.
	We can create a SEQUENCE number in descending order using a sequence object by setting increment property with -1. Example: --Create SeqenceDataDesc Sequence CREATE SEQUENCE dbo.SeqenceDataDesc AS INT           START WITH 1           INCREMENT BY -1 GO --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SeqenceDataDesc) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 1 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SeqenceDataDesc) AS NEXT_SEQUENCE_VALUE --> OUTPUT: 0 --Retrieve the next sequence value SELECT (NEXT VALUE FOR           dbo.SeqenceDataDesc) AS NEXT_SEQUENCE_VALUE --> OUTPUT: -1	We can create a IDENTITY number in descending order by setting increment size with -1 Example: --Insert a record INSERT INTO Supplier (SupplierName) VALUES('Govindaraj') --Retrieve current scope identity value SELECT SCOPE_IDENTITY() --> OUTPUT: 1 --Retrieve current identity value SELECT IDENT_CURRENT('Supplier') AS 'IDENTITY_CURRENT_VALUE' --> OUTPUT: 1 --Insert a record INSERT INTO Supplier (SupplierName) VALUES('Krishnaraj') --Retrieve current scope identity value SELECT SCOPE_IDENTITY() --> OUTPUT: 0 --Retrieve current identity value SELECT IDENT_CURRENT('Supplier') AS 'IDENTITY_CURRENT_VALUE' --> OUTPUT: 0 --Insert a record INSERT INTO Supplier (SupplierName) VALUES('Ramaraj') --Retrieve current scope identity value SELECT SCOPE_IDENTITY() --> OUTPUT: -1 --Retrieve current identity value SELECT IDENT_CURRENT('Supplier') AS 'IDENTITY_CURRENT_VALUE' --> OUTPUT: -1
	SEQUENCE Can be controlled by the application code.	IDENTITY Can’t be controlled by application code
	SEQUENCE object provides an option to enable caching, which greatly increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers. Defaults to CACHE.