Sql server® 2012 t-sql fundamentals

The Identity property and the Sequence Object

SQL Server supports two built-in solutions to automatically generate keys: the identity column prop-

erty and the sequence object. The identity property has been supported for as long as I can remem-

ber in SQL Server. It works well for some scenarios, but it also has many shortcomings. The sequence 

object was added in SQL Server 2012, and it resolves many of the identity property’s limitations. I’ll 

start with identity.

Identity

SQL Server allows you to define a property called identity for a column with any numeric type with a 

scale of zero (no fraction). This property generates values automatically upon INSERT based on a seed 

(first value) and an increment (step value) that are provided in the column’s definition. Typically, you 

would use this property to generate surrogate keys, which are keys that are produced by the system 

and are not derived from the application data.

www.it-ebooks.info

---