Q7.9: Identities, Sequential Keys, and Emulating Oracle Sequences

This has several sections, culled from various sources. It is better described as "Everything you've ever wanted to know about surrogate keys." It will serve to answer the following frequently asked questions:

What are the Features and Advantages of using Identities?
What are the Problems with and Disadvantages of Identities?
Common Questions about Identities How do I optimize the performance of a table that uses Identities?
How do I recover from a huge gap in my identity column?
How do I fix a table that has filled up its identity values?

OK, I hate identities. How do I generate sequential keys without using the Identity feature?
How do I optimize a hand-made sequential key system for best performance?

- Question 8.1 of the comp.database.sybase FAQ has a quick blurb about identities and sequential numbers. Search down in the page for the section titled, "Generating Sequential Numbers." Question 8.1 is a general document describing Performance and Tuning topics to be considered and thus doesn't go into as much detail as this page.

Advantages/Features of Using Identities

There's an entire section devoted to Identity columns in the SQL Server Reference manual, Chapter 5

Sybase System 10 introduced many changes over the 4.9.x architecture. One of these changes was the Identity feature. The identity column is a special column type that gets automatically updated by the server upon a new row insert. Its purpose is to guarantee a unique row identifier not based on the other data in the row. It was integrated with the server and made memory based for fast value retrieval and no locking (as was/is the case with homegrown sequential key generation schemes).

The Advantages and Features of Identities include:
Back to top
Disadvantages/Drawbacks of Using Identities

Despite its efficacy of use, the Identity has some drawbacks:
Back to top
Common questions about Identities

  • Is the Identity the equivalent of a Sequence in Oracle?

    Answer: More or less yes. The Identity guarantees a unique number to be assigned to a row in a table, as can an Oracle sequence. A sequence more approximates some of the sequential key generators described further below though, because its values are stored in an Oracle table. Oracle's sequence implementation differs from Sybase's Identity in several ways:

  • How do I configure Identities?: You can either create your table initially with the identity column:
    1> create table ident_test
    2> (text_field varchar(10),
    3>  ident_field numeric(5,0) identity)
    4> go
    Or alter an existing table and add an identity column:
    1> alter table existing_table
    2> add new_identity_field numeric(7,0) identity
    3> go
    When you alter a table and add an identity column, the System locks the table while systematically incrementing and adding unique values to each row. IF YOU DON'T SPECIFY a precision, Sybase defaults the size to 18! Thats 1,000,000,000,000,000,000-1 possible values and some major major problems if you ever crash your SQL server and burn a default number of values... (10^18 with the default burn factor will burn 5^14 or 500,000,000,000,000 values...yikes).

  • How do I Configure the burn factor?: The number of identity values that gets "burned" upon a crash or a shutdown can by found by logging into the server and typing:
    1> sp_configure "identity burning set factor"
    2> go

    the Default value set upon install is 5000. The number "5000" in this case is read as ".05% of all the potential identity values you can have in this particular case will be burned upon an unexpected shutdown." The actual number depends on the size of the identity field as you specified it when you created your table.

    To set the burn factor, type:

    1> sp_configure "identity burning set factor", [new value]
    2> go
    This is a static change; the server must be rebooted before it takes effect. NOTE: There is a performance tradeoff by tuning this parameter! The smaller the value, the greater the contention is on the OAM page storing the "block" of available values.

  • How do I tell which tables have identities?: You can tell if a table has identities one of two ways:

    1. sp_help [tablename]: there is a field included in the sp_help output describing a table called "Identity." It is set to 1 for identity fields, 0 otherwise.
    2. Within a database, execute this query:
      1> select object_name(id) "table",name "column", prec "precision"
      2> from syscolumns
      3> where convert(bit, (status & 0x80)) = 1   
      4> go
    this will list all the tables and the field within the table that serves as an identity, and the size of the identity field.

  • What is my identity burn factor vulnerability right now?:
    In other words, what would happen to my tables if I crashed my server right now?

    Identities are created type numeric, scale 0, and precision X. A precision of 9 means the largest identity value the server will be able to process is 10^9-1, or 1,000,000,000-1, or 999,999,999. However, when it comes to Burning identities, the server will burn (based on the default value of 5000) .05% of 1,000,000,000 or 500,000 values in the case of a crash. (You may think an identity precision allowing for 1 Billion rows is optomistic, but I once saw a precision set at 14...then the database crashed and their identity values jumped 5 TRILLION. Needless to say they abandoned their original design. Even worse, SQL server defaults precision to 18 if you don't specify it upon table creation...thats a MINIMUM 10,000,000,000 jump in identity values upon a crash with the absolute minumum burn factor)

    Lets say you have inserted 5 rows into a table, and then you crash your server and then insert 3 more rows. If you select all the values of your identity field, it will look like this:
    1> select identity_field from id_test
    2> go
    (8 rows affected)
    Here's your Identity burning options (based on a precision of 10^9 as above):
    Burn value	% of values	# values burned during crash
    5000		.05%		500,000
    1000		.01%		100,000
    100		.001%		10,000
    10		.0001%		1,000
    1 		.00001%		100
    So, the absolute lowest amount of numbers you'll burn, assuming you configure the burn factor down to 1 (sp_configure "identity burning set factor", 1) and a precision of 9, is 100 values. Back to
    Optimizing your Identity setup for performance and maintenance

    If you've chosen to use Identities in your database, here are some configuration tips to avoid typical Identity pitfalls:
    Back to top
    Recovery from Large Identity value gaps or
    Recovery from Identity insert errors/Full Identity tables

    This section will discuss how to re-order the identity values for a table following a crash/abnormal shutdown that has resulted in huge gaps in the values. The same procedure is used in cases where the identity field has "filled up" and does not allow inserts anymore. Some applications that use Identities are not truly candidates for this process (i.e., applications that depend on the identity field for business purposes as opposed to simple unique row identifiers). Applications like this that wish to rid their dependence on identities will have to re-evaluate their database design.

    Back to top
    How do I generate Sequential Keys w/o the Identity feature?

    There are many reasons not to use the Identity feature of Sybase. This section will present several alternative methods, along with their advantages and drawbacks. The methods are presented in increasing order of complexity. The most often implemented is Method 3, which is a more robust version of Method 2 and which uses a surrogate-key storage table.

    Throughout this section the test table i'm adding lines to and generating sequential numbers for is table inserttest, created like this:
    1> create table inserttest
    2> (testtext varchar(25), counter int)
    3> go
    Back to top
    Optimizing your home grown Sequential key generating process for any version of Sybase