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.

• 0800 hrs. Create table burn1(col1 char(4), col2 numeric identity). Inserted several rows. Select * from burn1 returns:

  col1 col2
  ---- --------------------
  aaaa                    1
  aaab                    2
  aaac                    3
  aaad                    4

  When the first insert is done, a block of 500000000000000 numbers is taken as the first block of numbers.

• 0815 hrs. shutdown with nowait.

  With the nowait, the server is unable to write the current identity number back to disk, so the rest of the 500000000000000 block of numbers is lost.

• 0816 hrs. startserver

• 0817 hrs. insert 1 row into burn1. select * returns:

  col1 col2
  ---- --------------------
  aaaa                    1
  aaab                    2
  aaac                    3
  aaad                    4
  aaae      500000000000002

  As expected, we lost the first block of 5000000000000 numbers. The insert of 1 row above causes the server to get the next block of 500000000000000 numbers.

• 0818 hrs. Change identity burning set factorfrom 5000 to 10.

  1> sp_configure "identity burning set factor", 10
  2> go

  Configuration option changed.  (return status = 0)

  1> sp_configure
  2> go

  name                          minimum     maximum     config_
  value run_value
  ---------------------------- ----------- ----------- ------------ ----
  -------
  ...
  ...
  identity burning set factor            1     9999999           10
      5000 

• 0820 hrs. shutdown with nowait again.

  With the nowait, the server is unable to write the current identity number back to disk, so the rest of the second 500000000000000 block of numbers is lost.

• 0822 hrs. startserver

• 0823 hrs. Check that identity burning set factor has changed.

  1> sp_configure
  2> go

  name                          minimum     maximum     config_
  value run_value
  ---------------------------- ----------- -------
  ---- ------------ -----------
  ...
  ...
  identity burning set factor            1     9999999           10
            10 

• 0825 hrs. insert 1 row into burn1. select * returns:

  col1 col2
  ---- --------------------
  aaaa                    1
  aaab                    2
  aaac                    3
  aaad                    4
  aaae      500000000000002
  aaaf     1000000000000003 

  As expected, the server lost the second block of 5000000000000 numbers. The insert of 1 row above causes the server to get the next block of 1000000000000 numbers.

  ---

  
  Note:

  The above output from the select, which shows that the server deleted the second large block of numbers, often confuses users into believing that no change to the identity parameter occurred.

  ---

• 0827 hrs. shutdown with nowait again.

• 0830 hrs. startserver

• 0825 hrs. insert 1 row into burn1. select * displays:

  col1 col2
  ---- --------------------
  aaaa                    1
  aaab                    2
  aaac                    3
  aaad                    4
  aaae      500000000000002
  aaaf     1000000000000003
  aaag     1001000000000004

  As expected, the server lost the first block of 10000000000 numbers. The insert of 1 row above causes the server to get the next block of 1000000000000 numbers.

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.