SQL Server Portal

S.No

Identity

Sequence

1

Dependant on table.

Independent from table.

2

Identity is a property in a table.

Example :

CREATE TABLE Table
test_Identity

(

[ID] int Identity (1,1),

[Product Name] varchar(50)

)

Sequence is an object.

Example :

CREATE SEQUENCE [dbo].[Sequence_ID]

AS [int]

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 1000

NO CYCLE

NO CACHE

3

If you need a new ID from an identity column you need to
insert and then get new ID.

Example :

Insert into [test_Identity] Values (‘SQL Server’)

GO

SELECT @@IDENTITY AS ‘Identity’

–OR

Select SCOPE_IDENTITY() AS ‘Identity’

In the sequence, you do not need to insert new ID, you can view the new ID directly.

Example :

SELECT NEXT VALUE
FOR dbo.[Sequence_ID]

4

You cannot perform a cycle in identity column. Meaning, you cannot restart the counter after a
particular interval.

In the sequence, you can simply add one property to make it a cycle.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CYCLE;

5

You cannot cache Identity column property.

Sequence can be easily cached by just setting cache property of
sequence. It also improves the performance.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CACHE 3;

6

You cannot remove the identity column from the table directly.

The sequence is not table dependent so you can easily remove it

Example :

Create table dbo.[test_Sequence]

(

[ID] int,

[Product Name] varchar(50)

)

GO

–First Insert With Sequence object

INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (NEXT VALUE FOR [Ticket] , ‘MICROSOFT SQL SERVER 2008’)

GO

–Second Insert without Sequence

INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (2 , ‘MICROSOFT SQL SERVER 2012’)

7

You cannot define the maximum value in identity column it is
based on the data type limit.

Here you can set up its maximum value.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

MAXVALUE 2000;

8

You can reseed it but cannot change the step size.

Example :

DBCC CHECKIDENT (test_Identity, RESEED, 4)

You can reseed as well as change the step size.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

RESTART WITH 7

INCREMENT BY 2;

9

You cannot generate range from identity.

You can generate a range of sequence
values from a sequence object with the help of sp_sequence_get_range.