Sequence vs identity

Question!

SQL Server 2012 introduced Sequence as a new feature, same as in Oracle and Postgres. Where sequences are preferred over identities? And why do we need sequences?



Answers

Identity and Sequence both are used to generate auto-incrementing numbers. following are the differences:-

  1. Sequence is independent from table and Identity is a table dependent
  2. In Sequence New value can be obtain before an INSERT statement but Identity values generated when a row inserted into table
  3. In Sequence New values can be generate in an UPDATE statement but in Identity new value can not be generate in an UPDATE statement
  4. In Sequence, For performance point of view, you can set cache size option and also you can define min and max values, can allow cycling but You cannot define minimum and maximum values,allow cycling, and caching options or not in Identity column

You can find more about Sequence click here



This video can help you solving your question :)
By: admin