Creating the books table (from the example above) with an identity_gap value of 1000, overrides the server-wide identity burning set factor setting that resulted in blocks of 500,000,000,000,000 ID numbers. Instead, ID numbers are allocated in memory in blocks of 1000.
The server allocates the first 1000 numbers and stores the highest number of the block (1000) to disk. When all the numbers are used, Adaptive Server takes the next 1000 numbers, starting with 1001, and stores the number 2000 as the highest number.
If Adaptive Server fails after row number 1002, it uses the numbers 1000 through 1002: numbers 1003 through 2000 are lost. When you restart Adaptive Server, it creates ID numbers starting from the highest number stored on the table’s OAM page plus one (2000), which leaves a gap of only 998 numbers.
You can significantly reduce the gap in ID numbers by setting the identity_gap for a table instead of using the server-wide table burning set factor. However, if you set this value too low, each time the server must write the highest number of a block to disk, which affects performance. For example, if identity_gap is set to 1, which means you are allocating one ID number at a time, the server must write the new number every time a row is created, which may reduce performance because of page lock contention on the table. You must find the best setting to achieve the optimal performance with the lowest gap value acceptable for your situation.
