What is the Difference between Identity and Sequence in SQL Server?

Leave a comment (1) Go to comments

What is Sequence in SQL Server ?

SQL Server 2011, Denali, introduces a new alternative to identity columns. There is a new CREATE SEQUENCE statement that can be used to create global sequence generators . This quite similar feature which already exists in Oracle 11g and DB2.

What is the Difference between Identity and Sequence ?

Identity Sequence
Table Specific Table Independent
You cannot obtain the new value in your application before using it You can obtain the new value before using it in an INSERT statement
You cannot add or remove the property from an existing column You can add or remove a default constraint defined for a column with an expression that generates a new sequence value (extension to the standard)
You cannot generate new values in an UPDATE statement when needed, rather only in INSERT statements You can generate new values in an UPDATE statement
The semantics of defining ordering in a multi-row insert are confusing, and in SELECT INTO statements are actually not guaranteed The semantics of defining ordering in a multi-row insert are very clear using an OVER clause (extension to the standard), and are even allowed in SELECT INTO statements
You cannot define: minimum and maximum values, whether to allow cycling, and caching options You can define minimum and maximum values, whether to allow cycling, and a cache size option for performance (extension to the standard)
You can reseed an identity property, but you cannot change the step size You can alter any of the properties of a sequence object besides the data type, including the current value, increment, minimum value, maximum value, cycle and cache size
You cannot obtain a whole range of new identity values in one shot, letting the application assign the individual values You can obtain a whole range of new sequence values in one shot using the stored procedure sp_sequence_get_range (extension to the standard), letting the application assign the individual values for increased performance

Tags : Difference between Identity and Sequence, Identity versus Sequence, Identity vs Sequence sql server,Sequence in sql server


If you liked this post then,

Subscribe to this Blog via Email:

Click here to Subscribe to FREE email updates from “DBATAG “, so that you do not miss out anything on SQL Server !!!

EOF - What is the Difference between Identity and Sequence in SQL Server?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. All features of SQL Server IDENTITY property of columns. And a handy procedure to monitor all Identity columns. http://www.sqllion.com/2011/08/identity-in-sql/

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.