Difference between Identity and Sequence in SQL Server 2012

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.

The following is a comparative study about identity and Sequences.

No		Identity	Sequence
1	Usage	Identity will spawn unique number in a table.	Sequence will spawn unique number in a database.
2	Type	An Identity is an attribute for a column.	A sequence is a database object.
3	Behavior	Generates values when rows are inserted.	An apps or SQL Server can fetch the next sequence without inserting the row using NEXT VALUE FORfunction.
4	Instances	Only one identity column per table is allowed.	You can have more than one sequence on a table.
5	Dependency	Identity is specific or limited to a single table.	Sequences are independent of tables.
6	Speed	Identity is slower.	Sequence is significantly faster as it reads from memory rather than from the disk.
7	Next value	Identity does not allow retrieving the next value.	Using NEXT VALUE FORfunction one can retrieve next value. SELECT NEXT VALUE FOR DEMOSEQ
8	Current value	Following ways to get the current value of an identity column. 1.  DBCC CHECKIDENT (‘DemoId’); 2.  Select @@IDENTITY3.  SELECT SCOPE_IDENTITY()4.  SELECT IDENT_CURRENT(‘DEMOID’);	To get the current value of a Sequence.SELECT CURRENT_VALUE FROM SYS.SEQUENCES WHERE NAME=’DEMOSEQ’
9	Syntax	IDENTITY [ (seed , increment ) ]One can set only Seed value (Initial value) and value to Increment.	CREATE SEQUENCE [schema_name . ] sequence_name [ AS [ built_in_integer_type | user-defined_integer_type ] ] [ START WITH ] [ INCREMENT BY ] [ { MINVALUE [ ] } | { NO MINVALUE } ] [ { MAXVALUE [ ] } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] [ { CACHE [ ] } | { NO CACHE } ] [ ; ]One can set minvalue, maxvalue , and can opt to cycle and cache.
10	Update	We can change the seed value using the following syntax, but can not modify the increment value.One canDBCC CHECKIDENT ( table_name [, { NORESEED | { RESEED [, new_reseed_value ] } } ] )	Except the data type, we can alter other properties using ALTER SEQUENCE command.ALTER SEQUENCE [schema_name. ] sequence_name [ RESTART [ WITH ] ] [ INCREMENT BY ] [ { MINVALUE } | { NO MINVALUE } ] [ { MAXVALUE } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] [ { CACHE [ ] } | { NO CACHE } ] [ ; ]
11	System catalog	All information about identity is available in sys.identity_columns	All information about sequence is available in sys.sequences