Identity Vs 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 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 VALUEFOR [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.