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 ?
|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,