Switch to standard view 
  Sybase logo
 
 
 



Background: What is the identity burning set factor?

The identity burning set factor configuration parameter is a percentage in decimal form x 10 million.

The default value, 5000, represents .05%, or .0005.

When an identity column is defined in a table, the server automatically assigns a unique sequential number for that identity column during inserts. To avoid excessive I/O, Adaptive Server Enterprise keeps blocks of numbers available in memoryfor use in identity values and keeps track of the current pointer in that block in memory.

The number of values in the block is controlled by the identity burning set factor. The identity column is a numeric datatype with a pre-defined precision. The server uses the possible range of values to determine which numbers are reserved.

For example, an identity column, "colA," is defined as numeric(7,0). The values of this column range from 1 to 9,999,999. Using the default burning set factor of 5000, .05% of the values are made available at a time. Therefore, the size of a block of numbers reserved in memory is .0005 (or .05%) x (9,999,999 +1) = 5000 values.

If the server shuts down unexpectedly, all unused numbers in the current reserved block (in memory) are lost. When the server restarts, the next insert starts with the next block. Therefore, abnormal shutdowns can cause large gaps in the identity column. An orderly shutdown (normal) does not delete identity values.



Note:

A shutdown with nowait is an unexpected shutdown and causes the block of numbers to be lost. For more information, see the Adaptive Server Enterprise System Administration Guide, the Transact-SQL User's Guide, and the Adaptive Server Enterprise Reference Guide.


Example: How Adaptive Server "loses" block values

In this example, we use the above identity column, colA, and perform a shutdown with nowait:

  1. After 5 inserts into the table we have:

    colA
    -------
     1
     2
     3
     4
     5
     

  2. We now shut down the server with nowait, then re-start the server.

    select colA from table: 

    colA
    -------
      1
      2
      3
      4
      5
    

  3. We perform an insert into the table, then select colA again:

    colA
    -------
      1
      2
      3
      4
      5
    5002

  4. If we perform a shutdown with nowait again, then repeat the insert and select colA again, colA displays as follows:

    colA
    -------
      1
      2
      3
      4
      5
    5002
    10003




Note:

The starting value in the next block almost always will not be the first value in the new block. This is because of a current value that is stored in the dbtable info. When the block is lost, the server goes to the next block and the next value in the dbtable info. In the above case, the last value in the dbtable was 1.


Correcting identity column gaps

You can fix the rows containing the large numbers to correct gaps in the identity column. Perform the following steps:

  1. Turn on identity_insert.

    1> set identity_insert table_name on
    
    2> go

  2. Record the row(s) information. Because you cannot update an identity column, you must delete the row(s) and insert them again with the correct identity values.

  3. Correct all rows in error.

  4. bcp out the data from the table.

  5. Turn off identity_insert.

    1> set identity_insert table_name off
    
    2> go

  6. Drop the table and re-create it. This step resets the identity column's auto-start number to 1.

  7. bcp the data back into the table using the -E option to maintain the identity values that appear in the host file.



    Note:

    The -E option tells the server not to use the auto-insert identity numbers and to use the identity numbers included in the bcp data.


The next identity number should be one greater than the largest identity number in the table.


Understanding the identity grab size

The identity grab size parameter allows a server process to reserve a block of identity column values for inserts into tables with identity columns.

What this means is that a particular server process gets its own block of numbers to use. This is useful if a particular server process needs to insert contiguous identity values. Identity grab size is a server-wide parameter, so do not set it to a high value. Set identity grab size only to a value large enough to accommodate the largest group of records you want to insert into contiguous rows. The default is 1.

Troubleshooting the identity burning set factor

In this example, performing shutdown with nowait confuses the user into believing that the identity burning set factor did not re-set.

This last step verifies that the identity burning set factor change worked.

New Identity Gap feature starting with ASE 12

The identity column contains a unique ID number, generated by Adaptive Server, for each row in a table. Because of the way the server generates ID numbers by default, you may 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.

This parameter can be used when creating a table with create table or select into. To change the identity gap, use identity_gap with sp_chgattribute. To see the identity gap setting for a table, use sp_help.

Create Table Example:

Create table mytable (col1 char(10), col2 datetime, col3 numeric(7,0) identity) with identity_gap = 100

For details about using identity_gap, see the System Administration Guide and the command and system procedure descriptions in the Adaptive Server Reference Manual.



Back to Top
© Copyright 2010, Sybase Inc.