IDENTITY property

Providing simple counter-type values for tables that don’t have a natural or efficient primary key is common. Columns such as cust_id are usually simple counter fields. The IDENTITY property makes generating unique numeric values easy. IDENTITY isn’t a data type; it’s a column property that you can declare on a whole-number data type such as tinyint, smallint, int, bigint, or numeric/decimal (with which only a scale of zero makes any sense). Each table can have only one column with the IDENTITY property. The table’s creator can specify the starting number (seed) and the amount that this value increments or decrements. If not otherwise specified, the seed value starts at 1 and increments by 1, as shown in this example:

CREATE TABLE ...

Get Microsoft SQL Server 2012 Internals now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.