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