IDENTITY and Counter Values

InterSystems SQL enables you to define columns that are system-generated, such as the IDENTITY column, or that automatically increment upon each INSERT or UPDATE operation, such as the RowVersion, AutoIncrement, and Serial Counter columns.

If you insert a value into one of these columns, the INSERT operation might fail, depending on the column type.

Column Type	INSERT Allowed?
IDENTITY	Not by default. To configure IDENTITY to accept inserted values, set the %CLASSPARAMETER ALLOWIDENTITYINSERT=1 value when defining the table. For more details, see Creating Named RowId Column Using IDENTITY Keyword.
ROWVERSION	No user-specified, calculated, or default value can be inserted for a ROWVERSION column.
SERIAL AUTO_INCREMENT	Yes. If you specify a positive integer value, INSERT inserts the value into the column, overriding the default counter value. If you specify no value, 0 (zero), or a nonnumeric value, INSERT ignores the specified value, increments this column’s value by 1, and inserts that value into the column.

Column Type

INSERT Allowed?

IDENTITY

Not by default.

To configure IDENTITY to accept inserted values, set the %CLASSPARAMETER ALLOWIDENTITYINSERT=1 value when defining the table. For more details, see Creating Named RowId Column Using IDENTITY Keyword.

ROWVERSION

No user-specified, calculated, or default value can be inserted for a ROWVERSION column.

SERIAL

AUTO_INCREMENT

Yes.

If you specify a positive integer value, INSERT inserts the value into the column, overriding the default counter value.

If you specify no value, 0 (zero), or a nonnumeric value, INSERT ignores the specified value, increments this column’s value by 1, and inserts that value into the column.

Computed Values

List Values