The IDENTITY keyword causes the columns to be automatically incremental, meaning that each successive insert into the table will automatically assign a value greater than any previous row of the table. These columns are often referred to as "autoincrement columns". The assigned values are normally consecutive integers. There may be gaps in the sequence if some transactions that reserve a number fail to commit.

An autoincrement column may or may not be part of the primary key or of any index.

The identity_value() function returns the identity column value most recently assigned on the current connection. This can be used inside procedures or from clients. See also the ODBC statement option SQL_GETLASTSERIAL.

The set_identity_column() function allows you to change the identity column sequence value.

The IDENTITY keyword supports the SQL 3 optional clause that allows you to specify a starting value and/or step value in the table creation statement. The syntax is:

      IDENTITY (START WITH <N>, INCREMENT BY <Y>)

CREATE TABLE DB.DBA.AI (
	AI INTEGER IDENTITY,
	XX VARCHAR,
	PRIMARY KEY (AI)
  )
;

set_identity_column("DB"."DBA"."AI", "AI", 11);

CREATE TABLE DB.DBA.AI (
	AI INTEGER IDENTITY (START WITH 100),
	XX VARCHAR,
	PRIMARY KEY (AI)
  )
;

	See Also
	Sequence Objects