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 datatype; it's a column property that you can declare on a whole-number datatype such as tinyint, smallint, int, or numeric/decimal (having a scale of zero). 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 customer
(
cust_id smallint IDENTITY NOT NULL,
cust_name varchar(50) NOT NULL
)

To find out which seed and increment values were defined for a table, you can use the IDENT_SEED(tablename) and IDENT_INCR(tablename) functions. The statement

SELECT IDENT_SEED('customer'), IDENT_INCR('customer')

produces

1 1

for the customer table because values weren't explicitly declared and the default values were used.
This next example explicitly starts the numbering at 100 (seed) and increments the value by 20:

CREATE TABLE customer
(
cust_id smallint IDENTITY(100, 20) NOT NULL,
cust_name varchar(50) NOT NULL
)
Download