IDENTITY Property

It is common to provide simple counter-type values for tables that don’t have a natural or efficient primary key. 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 ...

Get Microsoft® SQL Server® 2008 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.