Labels

Wednesday, 23 December 2015

SQL Server - Difference between Identity and Sequence in SQL Server 2012

The below mentioned points are the differences between Identity and Sequence .

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 we need a new ID from an identity column we 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, we do not need to insert new ID
we can view the new ID directly.


Example :

SELECT NEXT VALUE
FOR dbo.[Sequence_ID]
4



We cannot perform a cycle in identity column.
i.e, we cannot restart the counter after a
particular interval.




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

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]  CYCLE;
5




We 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


We cannot remove the identity column from the table directly.

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

7


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





Here we can set up its maximum value.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

MAXVALUE 2000;
8





We can reseed it but cannot change the step size.

Example :

DBCC CHECKIDENT (test_Identity, RESEED, 4)





We can reseed as well as change the step size.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

RESTART WITH 7

INCREMENT BY 2;
9 We cannot generate range from identity.

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

No comments:

Post a Comment