Manage Identity Gaps in Tables

The IDENTITY column contains a unique ID number, generated by SAP ASE, for each row in a table.

Because of the way the server generates ID numbers by default, you may occasionally have large gaps in the ID numbers. The identity_gap parameter gives you control over ID numbers, and potential gaps in them, for a specific table.

By default, SAP ASE allocates a block of ID numbers in memory instead of writing each ID number to disk as it is needed, which requires more processing time. The server writes the highest number of each block to the table’s object allocation map (OAM) page. This number is used as the starting point for the next block after the currently allocated block of numbers is used or “burned.” The other numbers of the block are held in memory, but are not saved to disk. Numbers are considered burned when they are allocated to memory, then deleted from memory, either because they were assigned to a row, or because they were erased from memory due to some abnormal occurrence such as a system failure.

Allocating a block of ID numbers improves performance by reducing contention for the table. However, if the server fails or is shut down with no wait before all the ID numbers are assigned, the unused numbers are burned. When the server is running again, it starts numbering with the next block of numbers based on the highest number of the previous block that the server wrote to disk. Depending on how many allocated numbers were assigned to rows before the failure, you may have a large gap in the ID numbers.

Identity gaps can also result from dumping and loading an active database. When dumping, database objects are saved to the OAM page. If an object is currently being used, the maximum used identity value is not in the OAM page and, therefore, is not dumped.

• Parameters for Controlling Identity Gaps
  SAP ASE provides parameters that allow you to control gaps in identity numbers.
• Comparison of identity burning set factor and identity_gap
  The identity_gap parameter controls the size of identity gaps for a particular table.
• Set the Table-Specific Identity Gap
  Set the table-specific identity gap when you create a table using either create table or select into.
• Change the Table-Specific Identity Gap
  To change the identity gap for a specific table, use sp_chgattribute.
• Display Table-Specific Identity Gap Information
  To see the identity_gap setting for a table, use sp_help.
• Gaps from Other Causes
  Manually inserting values into the IDENTITY column, deleting rows, setting the identity grab size value, and rolling back transactions can create gaps in IDENTITY column values. Setting the identity burning set factor does not affect these gaps.
• IDENTITY Column Maximum Value
  The maximum number of rows you can insert into a table depends on the precision that is set for the IDENTITY column.