0

I am using Sybase Adaptive Server Enterprise 15.7 and I have created a table like

create table student(
rollNum int identity,
name varchar(16),
primary key(rollNum)
)with identity_gap = 50

When records are inserted, rollNum jumps

from 3 to 51 --> where it should have been jumped to 53 maintaining a gap of 50.

from 60 t0 101 --> where it should have been jumped to 110 maintaining a gap of 50.

Is this behaviour expected or am I missing something ?.

Bifrost
  • 417
  • 5
  • 23

1 Answers1

1

You're misunderstanding how the identity gap setting works. What happens is your identity gap value (50) is taken as a block at once into memory and then be allocated as required (i.e. values 1 to 50). When the values are all used, then a new block would be taken at that point.

If the instance is killed before all the first block was used restarted the next block is allocated (51-100 since 1-50 was previously allocated) and so the next identity value set is 51.

The identity gap setting ensures that you never get more than the value set of 50 as a jump between values not that it's an absolute gap value.

This is why it's important not to set the identity gap too low on heavily inserted tables, as there could be a small performance hit in constantly allocating small blocks of values each time all values are taken. For example on a very heavily hit table you may want to consider an identity gap of 1000 or even 10000 to prevent constant allocation of blocks of values.

Rich Campbell
  • 566
  • 2
  • 9