@Amatya Sir,
Sequence has been introduced since SQL Server 2012(code name: Denali).
Sequence is used to generate a sequence of numeric values in either ascending or descending order, along with an option to have the minimum and maximum values and also allows to cycle and cache for better performance.
An
Identity is an attribute for a column that generates values when rows are inserted.
Long time back I wrote an article on the same (
http://www.codeproject.com/Articles/162620/An-Introduction-to-Sql-Code-Name-Denali-Part-II?msg=3792232#3.4 )
Anyways, an excerpt from that article which is relevant to the question being asked
Comparison between Sequence and Identity Column 1)Identity Column is table specific while Sequence is table independent
2)We can create a range of sequence using sp_sequence_get_range which is not possible using identity column
3)In case of sequence we can define the boundary using the Minimum and Maximum Value. While the same is not possible in case of identity
4)Cycling is there in sequence and absent in Identity column
5) Sequence gives better performance as compared to Identity Column since it reads from memory rather than from the disk.
6) We can grant permission like Alter, control, references, update, Take Ownership and View definition to the sequence objects.
7) All information about identity is available in
sys.identity_columns while all information about sequence is available in
sys.sequences 8) Only one identity column per table is allowed while we can have more than one sequence on a table.
9) Identity will generate unique number in a table. Sequence will generate unique number in the database.
Hope this helps
--
Thanks & Regards,
RNA Team
Amatya, if this helps please login to Mark As Answer. | Alert Moderator