SQL Server Interview: Difference between Sequence Object and Identity Column

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

This is a common interview question for SQL Server Database Developer. SQL Database Developer must prepare for this.

Sequence and identity, both used to generate an auto number, but the major difference is Identity is a table dependant and Sequence is independent of a table.

A Sequence Object introduced in SQL Server 2012 and Identity column introduced in SQL Server 6.0.

A Sequence is a User defined object and Identity column is a table property.

The same Sequence object can use with the multiple tables. The same Identity column cannot use with multiple tables, we can define identity column on each table individually.

If you define the same Sequence Object to multiple tables, it generates the sequence number database-wide and maintains a unique sequence number across the multiple tables. An Identity column generates a unique number at table only.

Sequence syntax (Start with 1 and Increment by 1):

1 2 3 4 5

CREATE SEQUENCE dbo.Sequence_Name AS INT START WITH 1 INCREMENT BY 1 GO

Identity syntax (Start with 1 and Increment by 1):

1 2 3 4

CREATE TABLE dbo.Table_Name ( Id INT IDENTITY(1,1), Name VARCHAR(50) ) GO

Check the next value of Sequence object:

1	SELECT (NEXT VALUE FOR dbo.Sequence_Name) AS SeqNextValue

Check the next value of Identity Column:

1	SELECT SCOPE_IDENTITY()

(write after use of Identity column)

Check the current value of Sequence Object:

1 2 3

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

Check the current value of Identity Column:

1 2	SELECT IDENT_CURRENT('Table_Name') AS 'IdentityCurrentValue'

You can modify a Sequence object using ALTER Statement.

1 2 3 4

ALTER SEQUENCE dbo.Sequence_Name RESTART WITH 100 INCREMENT BY 5 GO

You can only reset the value of an Identity column using DBCC CHECKIDENT().

1	DBCC CHECKIDENT('Table_Name', RESEED,100)

In Sequence New values can be generated in an UPDATE statement, but in Identity new value cannot be generated in an UPDATE statement.

You can define a maximum value for Sequence Object (Using MAXVALUE). You cannot provide a maximum value to Identity Column.

Once your Sequence object reached to MAXVALUE, you can also define a CYCLE option to restart it from the beginning. You cannot restart an Identity column value.

You can also enable data CACHE for a Sequence Object. You cannot enable data CACHE for an Identity Column and it uses table related data cache.

A User must require an access permission for Sequence Object. For Identity column, User requires only table related permissions.

Performance wise, Identity column is 3x faster than Sequence object.