docs.intersystems.com
Using Caché SQL
RowVersion and Serial Counter Fields
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Search:    

RowVersion and Serial Counter Fields
InterSystems SQL supports two special-purpose data types for automatically-incrementing counter values:
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.RowVersion) data type maps to BIGINT (%Library.BigInt).
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.Counter 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.
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.


Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA