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,
Click here to Subscribe to FREE email updates from “DBATAG “, so that you do not miss out anything on SQL Server !!!
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/