Skip to main content

RowVersion and Serial Counter Fields

RowVersion and Serial Counter Fields

InterSystems SQL supports two special-purpose data types for automatically-incrementing counter values:

  • A field of data type ROWVERSION counts inserts and updates to all RowVersion tables namespace-wide. Only inserts and updates in tables that contain a ROWVERSION field increment this counter. ROWVERSION values are unique and non-modifiable. This namespace-wide counter never resets.

  • A field of data type SERIAL (%Library.CounterOpens in a new tab) counts inserts to the table. By default, this field receives an automatically incremented integer. However, a user can specify a value to this field.

RowVersion Field

The RowVersion field is an optional user-defined field that provides row-level version control, allowing you to determine the order in which changes were made to the data in each row namespace-wide. Caché maintains a namespace-wide counter, and assigns a unique incremental positive integer to this field each time the row data is modified (insert, update, or %Save). Because this counter is namespace-wide, an operation on one table with a ROWVERSION field sets the increment point for the ROWVERSION counter that is used for all other tables with a ROWVERSION field in the same namespace.

You create a RowVersion field by specifying a field of data type ROWVERSION. You can only specify one ROWVERSION data type field per table. Attempting to create a table with more than one ROWVERSION field results in a 5320 compilation error.

This field can have any name and can appear in any column position. The ROWVERSION (%Library.RowVersionOpens in a new tab) data type maps to BIGINT (%Library.BigIntOpens in a new tab).

This field receives a positive integer from an automatically incremented counter, starting with 1. This counter increments whenever data in any ROWVERSION-enabled table is modified by an insert, update, or %Save operation. The incremented value is recorded in the ROWVERSION field of the row that has been inserted or updated.

A namespace can contain tables with a RowVersion field and tables without this field. Only data changes to tables that have a RowVersion field increment the namespace-wide counter.

When a table is populated with data, Caché assigns sequential integers to this field for each inserted row. If you use ALTER TABLE to add a ROWVERSION field to a table that already contains data, this field is created as NULL for pre-existing fields. Any subsequent insert or update to the table assigns a sequential integer to the RowVersion field for that row. This field is read-only; attempting to modify a RowVersion value generates an SQLCODE -138 error: Cannot INSERT/UPDATE a value for a read only field. Therefore, a RowVersion field is defined as unique and non-modifible, but not required or non-null.

RowVersion values always increment. They are not reused. Therefore, inserts and updates assign unique RowVersion values in temporal sequence. Delete operations remove numbers from this sequence. Therefore, RowVersion values may not be numerically contiguous.

This counter is never reset. Deleting all table data does not reset the RowVersion counter. Even dropping all tables in the namespace that contain a ROWVERSION field does not reset this counter.

The RowVersion field should not be included in a unique key or primary key. The RowVersion field cannot be part of an IDKey index.

The RowVersion field is not hidden (it is displayed by SELECT *).

This is shown in the following example of three tables in the same namespace.

  1. Create Table1 and Table3, each of which has a ROWVERSION field, and Table2 that does not have a ROWVERSION field.

  2. Insert ten rows into Table1. The ROWVERSION values of these rows are the next ten counter increments. Since the counter has not previously been used, they are 1 through 10.

  3. Insert ten rows into Table2. Because Table2 does not have a ROWVERSION field, the counter is not incremented.

  4. Update a row of Table1. The ROWVERSION values for this row is changed to the next counter increment (11 in this case).

  5. Insert ten rows into Table3. The ROWVERSION values of these rows are the next ten counter increments (12 through 21).

  6. Update a row of Table1. The ROWVERSION values for this row is changed to the next counter increment (22 in this case).

  7. Delete a row of Table1. The ROWVERSION counter is unchanged.

  8. Update a row of Table3. The ROWVERSION values for this row is changed to the next counter increment (23 in this case).

Serial Field

You can use the SERIAL data type (%Library.CounterOpens in a new tab in a persistent class table definition) to specify one or more optional integer counter fields to record the order of inserts of records into a table. By default, this field receives a positive integer from an automatically incremented table counter whenever a row is inserted into the table. However, a user can specify an integer value for this field during an insert, overriding the table counter default.

  • If an INSERT does not specify a value for the counter field, it automatically receives a positive integer counter value. Counting starts from 1. Each successive value is an increment of 1 from the highest allocated counter value for this field.

  • If an INSERT specifies an integer value for the counter field, the field receives that value. It can be a positive or negative integer value, can be lower or higher than the current counter value, and can be an integer already assigned to this field. If this value is higher than any assigned counter value, it sets the increment starting point for the next automatically assigned counter to that value.

Attempting to UPDATE a counter field value results in an SQLCODE -105 error.

This counter is reset to 1 by the TRUNCATE TABLE command. It is not reset by a DELETE command, even when the DELETE command deletes all rows in the table.

FeedbackOpens in a new tab