Creating Named RowId Column Using IDENTITY Keyword

InterSystems SQL automatically creates a RowID column for each table, which contains a system-generated integer that serves as a unique record id. The optional IDENTITY keyword allows you to define a named column with the same properties as a RowID record id column. An IDENTITY column behaves as a single-column IDKEY index, whose value is a unique system-generated integer.

Defining an IDENTITY column prevents the defining of the Primary Key as the IDKEY.

Just as with any system-generated ID column, an IDENTITY column has the following characteristics:

You can only define one column per table as an IDENTITY column. Attempting to define more than one IDENTITY column for a table generates an SQLCODE -308 error.
The data type of an IDENTITY column must be an integer data type. If you do not specify a data type, its data type is automatically defined as BIGINT. You can specify any integer data type, such as INTEGER or SMALLINT; BIGINT is recommended to match the data type of RowID. Any specified column constraints, such as NOT NULL or UNIQUE are accepted but ignored.
Data values are system-generated. They consist of unique, nonzero, positive integers.
By default, IDENTITY column data values cannot be user-specified. By default, an INSERT statement does not, and cannot, specify an IDENTITY column value. Attempting to do so generates an SQLCODE -111 error. To determine whether an IDENTITY column value can be specified, call the $SYSTEM.SQL.Util.GetOption("IdentityInsert")Opens in a new tab method; the default is 0. To change this setting for the current process, call the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("IdentityInsert",1,.oldval). You can also specify %CLASSPARAMETER ALLOWIDENTITYINSERT=1 in the table definition. Specifying ALLOWIDENTITYINSERT=1 overrides any setting applied using SetOption("IdentityInsert"). For further details, refer to the INSERT statement.
IDENTITY column data values cannot be modified in an UPDATE statement. Attempting to do so generates an SQLCODE -107 error.
The system automatically projects a primary key on the IDENTITY column to ODBC and JDBC. If a CREATE TABLE or ALTER TABLE statement defines a primary key constraint or a unique constraint on an IDENTITY column, or on a set of columns including an IDENTITY column, the constraint definition is ignored and no corresponding primary key or unique index definition is created.
A SELECT * statement does return a table's IDENTITY column.

Following an INSERT, UPDATE, or DELETE operation, you can use the LAST_IDENTITY function to return the value of the IDENTITY column for the most-recently modified record. If no IDENTITY column is defined, LAST_IDENTITY returns the RowID value of the most recently modified record.

These SQL statements create a table with an IDENTITY column and insert a rows into that table, generating an IDENTITY column value for the created table:

CREATE TABLE Employee (
    EmpNum INT NOT NULL,
    MyID IDENTITY NOT NULL,
    Name VARCHAR(30) NOT NULL,
    CONSTRAINT EmployeePK PRIMARY KEY (EmpNum))

INSERT INTO Employee (EmpNum,Name) 
SELECT ID,Name FROM SQLUser.Person WHERE Age >= '25'

In this case, the primary key, EmpNum, is taken from the ID column of another table. EmpNum values are unique integers, but because of the WHERE clause, this column might contain gaps in the sequence. The IDENTITY column, MyID, assigns a user-visible unique sequential integer to each record.

Sharded Table Restrictions

Bitmap Extent Index