Microsoft® SQL Server® 2008 Internals

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 ...