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

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:

• Sequences are not memory-resident and thus are not subjected to the same "gap" problems that Sybase's identity values are. (Sequences can skip numbers on rollbacks, but sequence gaps are limited to these cases).
• A Sequence is actually an object in an Oracle tablespace, created like any other database object. You can specify a seed value, a min and a max, and the increment value. Identities are memory-based constructs with no configuring of min and max values. You can seed a value though by specifying the start value.
• A sequence creation results in an entry into the sequences system tables in Oracle, where you can see the config values, plus the last number used.
• You can see the last number inserted by the Identity by selecting @@identity. To see the last number inserted by a Sequence, you either have to select seq_name.currval from dual or select it from one of the system views.
• To insert a sequence number into a row, you have to specify it in the insert statement (insert into table values (val1,val2,test_seq.nextval)). An Identity automatically inserts the next value without having to specify the Identity column in the insert statement. It is more transparent to the end user than Oracle's implementation. In addition, the auto identity feature makes even adding the use of identity to a table transparent to end users.
• A Sequence *can* be implemented as a row identifier, or it can be used for other purposes. It can be used to generate multiples of numbers (3,6,9,12 instead of 1,2,3,4) for example. Identity's whole purpose is to assign one unique number (NOT necessarily exactly sequential) per a row per a table.

1> create table ident_test
2> (text_field varchar(10),
3>  ident_field numeric(5,0) identity)
4> go

1> alter table existing_table
2> add new_identity_field numeric(7,0) identity
3> go

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

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
   

1> select identity_field from id_test
2> go
identity_field
--------------
1 
2
3
4
5
500006
500007
500008

(8 rows affected)

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

• Tune the burn factor!: see the vulnerabilty section for a discussion on what happens to identity values upon SQL server crashes. Large jumps in values can crash front ends that aren't equipped to handle and process numbers upwards of 10 Trillion. I've seen Powerbuilder applications crash and/or not function properly when trying to display these large identity values.
• Run update statistics often on tables w/ identities: Any index with an identity value as the first column in the search condition will have its performance severely hampered if Update statistics is not run frequently. Running a nightly update statistics/sp_recompile job is a standard DBA task, and should be run often regardless of the existance of identities in your tables.
• Tune the "Identity Grab Size": SQL server defaults the number of Identity values it pre-fetches to one (1). This means that in high insert environments the Server must constantly update its internal identity placeholder structure before adding the row. By tuning this parameter up:

  1> sp_configure "identity grab size", [number]
  2> go
  

  You can prefetch larger numbers of values for each user as they log into the server an insert rows. The downside of this is, if the user doesn't use all of the prefetched block of identity values, the unused values are lost (seeing as, if another user logs in the next block gets assigned to him/her). This can quickly accelerate the depletion of identity values and can cause gaps in Identity values.
  (this feature is new with System 11)
  
• Do NOT build business rules around Identity values. More generally speaking the recommendation made by DBAs is, if your end users are EVER going to see the identity field during the course of doing their job, then DON'T use it. If your only use of the Identity field is for its advertised purpose (that being solely to have a uniquely identifying row for a table to index on) then you should be fine.
• Do NOT build your clustered index on your Identity field, especially if you're doing lots of inserts. This will create a hot spot of contention at the point of insertion, and in heavier OLTP environments can be debilitating.

• Method 1:bcp out and in:
  - First, (from O/S command line):

  % bcp database..table out [data_file] -Usa -S[server] -N
  

  This will create a binary bcp datafile and will force the user to create a .fmt file. The -N option tells the server to skip the identity field while bcp'ing out.
  - drop and recreate the table in question from ddl (make sure your table ddl specifies the identity field).
  - Now bcp back in:

  % bcp database.table in [data_file -Usa -S[server] -f[fmt file] -N
  

  The -N option during bcp in tells the server to ignore the data file's placeholder column for the defined identity column.
  
  

  Coincidentally, if you bcp out w/o the -N option, drop the table, recreate from ddl specifying the identity field, and bcp back in w/o the -N option, the same effect as above occurs.

  (note: if you bcp out a table w/ identity values and then want to preserve the identity values during the bcp back in, use the "-E" option.)

• Method 2: select into a new table, adding the identity column as you go : Follow this process:
  

  1> select [all columns except identity column] 
  2> [identity column name ] = identity(desired_precision)
  3> into [new_table] 
  4> from [old table]
  5> go
  

• Method #3: Rob Verschoor published a nice article discussing Identity Gap recovery in the 4Q1998 Issue of the ISUG Technical Journal. This article describes a fast, efficient and painless recovery from Identity gaps through the use of a temporary key storage table system. The article is available at this link. This is probably the easiest of the methods.
  
  
• There are alternate methods that perform the above in multi steps, and might be more appropriate in some situations.
  • You can bcp out all the fields of a table except the identity column (create the bcp format file from the original table, edit out the identity column, and re-bcp). At this point you can create a new table with or without the identity column; if you create it with, as you bcp back in the Server will assign new identity values. If you create it without, you can bcp back in normally and then alter the table and add the identity later.
  • You can select all columns but the identity into a new table, then alter that table and add an identity later on.
  
  
  
• There's now an official Technote discussing Identity Gaps: Identity Burning Set Factor and Fixing Identity Gaps Explained . This is DocID 20113 in case the URL does not work.
  
  
• You are supposed to be able to use the (insofar as I can tell) undocumented dbcc command dbcc object_atts(objname, att_num, option [,put_value]) to reset identity_values. It requires the sybase_ts_role and has been reported to have limited success. For full syntax, click here (information from a Sybase-L post 10/15/97 by Peter Jones).

1> create table inserttest
2> (testtext varchar(25), counter int)
3> go

• Method 1: Create your table with a column called counter of type int. Then, each time you insert a row, do something like this:
  

  1> begin tran
  2> declare @nextkey int
  3> select @nextkey=max(counter)+1 from inserttest holdlock
  4> insert inserttest (testtext,counter) values ("test_text,@nextkey")
  5> go
  1> commit tran
  2> go
  

  This method is rather inefficient, as large tables will take minutes to return a max(column) value, plus the entire table must be locked for each insert (since the max() will perform a table scan). Further, the select statement does not guarantee an exclusive lock when it executes unless you have the "holdlock" option; so either duplicate values might be inserted to your target table or you have massive deadlocking.
  
  
• Method 2: See Question 10.1.1 of the comp.database.sybase FAQ is the May 1994 (Volume 3, Number 2) Sybase Technical Note (these links will open in a new browser window). Search down in the tech note for the article titled, "How to Generate Sequential Keys for Table Key Columns." This has a simplistic solution that is expanded upon in Method 3.
  
  
• Method 3: Create a holding table for keys in a common database: Here's our central holding table.

  1> create table keystorage
  2> (tablename varchar(25),
  4>  lastkey int)
  5> go
  

  And initially populate it with the tablenames and last values inserted (enter in a 0 for tables that are brand new).

  1> insert into keystorage (tablename,lastkey) 
  2> select "inserttest", max(counter) from inserttest
  3> go
  

  Now, whenever you go to insert into your table, go through a process like this:

  1> begin tran
  2> update keystorage set lastkey=lastkey+1 where tablename="inserttest"
  3> go
  
  1> declare @lastkey int
  2> select @lastkey = lastkey from keystorage where tablename="inserttest"
  3> insert inserttest (testtext,counter) values ("nextline",@lastkey)
  4> go
  
  1> commit tran
  2> go
  

  Most of the places I've implemented this process, we have a generic procedure (called get_next_key for example) that looks something like this:

  create procedure get_next_key
  @tablename varchar(50),
  @result int output
  as
  begin
  
  begin tran
    update key_storage set lastkey=lastkey+1 where tablename=@tablename
    select @result=lastkey from key_storage where tablename=@tablename
  
  commit tran
  end
  go
  

  And then when you call it from another piece of code (be it another Stored Procedure, or a spid in Powerbuilder, or inline SQL in VB, etc):

  declare @new_table_id int
  exec get_next_key "new_table",@new_table_id output
  insert into new_table values (@new_table_id, ...)
  

  And this populates an SQL variable with the next unique id to be used. Notice the defining of the "output" variable, and that you must specifically assign the output variable when you call get_next_key.

  There is plenty of room for error checking with this process: for example (code adapted from Colm O'Reilly (colm@mail.lk.blackbird.ie) post to Sybase-L 6/20/97) (Note: you can also use error checking like this to make the get_next_key process above more reliable).

  1> begin tran
  2>   update keystorage set lastkey=lastkey+1 where tablename="inserttest"
  3>   if @@rowcount=1
  4>   begin
  5>     declare @lastkey int
  6>     select @lastkey=lastkey from keystorage where tablename="inserttest"
  7>   end
  8> commit tran
  9> begin tran
  10>   if @lastkey is not null
  11>   begin
  12>     insert inserttest (testtext,counter) values ("third line",@lastkey)
  13>   end
  14> commit tran
  15> go
  

  This provides a pretty failsafe method of guaranteeing the success of the select statements involved in the process. You still have a couple of implementation decisions though:
  • One transaction or Two? The above example uses two transactions to complete the task; one to update the keystorage and one to insert the new data. Using two transactions reduces the amount of time the lock is held on keystorage and thus is better for high insertion applications. However, the two transaction method opens up the possibility that the first transaction will commit and the second will roll back, leaving a gap in the sequential numbers. (of course, this gap is small potatos compared to the gaps that occur in Identity values). Using one transaction (deleting lines 8 and 9 in the SQL above) will guarantee absolutely no gaps in the values, but will lock the keystorage table longer, reducing concurrency in high insert applications.
  • Update first or select first? The examples given generally update the keystorage table first, THEN select the new value. Performing the select first (you will have to rework the creation scheme slightly; by selecting first you're actually getting the NEXT key to add, where as by updating first, the keystorage table actually holds the LAST key added) you allow the application to continue processing while it waits for the update lock on the table. However, performing the update first guarantees uniqueness (selects are not exclusive).
  
  Some DBAs experienced with this keystorage table method warn of large amounts of blocking in high insert activity situations, a potential drawback. However, I've never run into performance problems using this scheme, and in fact have seen situations where this process has markedly INCREASED insert performance over existing select max(id) from table based processes.
  
  
• Method 4: Enhance the above method by creating an insert trigger on your inserttest table that performs the next-key obtainment logic. Or you could create an insert trigger on keystorage which updates the table and obtains your value for you. Integrating the trigger logic to your application might make this approach more complex. Also, because of the nature of the trigger you'll have to define the sequence number columns as allowing NULL values (a bad thing if you're depending on the sequential number as your primary key). Plus, triggers will slow the operation down because after obtaining the new value via trigger, you'll have to issue an extra update command to insert the rest of your table values.
  
  
• Other Methods: there are several other implementation alternatives available that involve more complex logic but which might be good solutions. One example has a central table that stores pre-inserted sequential numbers that are deleted as they're inserted into the production rows. This method allows the sequence numbers to be recycled if their associated row is deleted from the production table. An interesting solution was posted to Sybase-L 6/20/97 by Matt Townsend (mtowns@concentric.net) and is based on the millisecond field of the date/time stamp. His solution guarantees uniqueness without any surrogate tables or extra inserts/updates, and is a superior performing solution to other methods described here (including Identities), but cannot support exact sequential numbers. Some other solutions are covered in (originally) Malcolm Colton's White paper on Sequential Keys, which is primarily a P&T document (and is referenced below in the Optimizing section). This is DocID 860 in Sybase's technote site if the above link fails.

• First, read Malcolm Colton's excellent white paper entitled How Surrogate Primary Key Generation Affects Concurrency and the Cache Hit Ratio . This is a great overview of methods, concurrency, and some other performance issues. His original paper appears here, but the Technote paper contains updated information
  
  
• max_rows_per_page/fillfactor/table padding to simulate row level locking: This is the most important tuning mechanism when creating a hand -made sequence key generation scheme. Because of Sybase's page level locking mechanism, your concurrency performance in higher-insert activity situations could be destroyed unless the server only grabs one row at a time. However since Sybase doesn't currently have row-level locking, we simulate row-level locking by creating our tables in such a way as to guarantee one row per 2048 byte page.
  
  • For pre-System 11 servers; Calculate the size of your rows, then create dummy fields in the table that get populated with junk but which guarantee the size of the row will fill an entire page. For example (code borrowed from Gary Meyer's 5/8/94 ISUG presentation (gmeyer@netcom.com)):

    1> create table keystorage
    2> (tablename varchar(25),
    3>  lastkey int,
    4>  filler1 char(255) not null,
    5>  filler2 char(255) not null,
    6>  filler3 char(255) not null,
    7>  filler4 char(255) not null,
    8>  filler5 char(255) not null,
    9>  filler6 char(255) not null,
    9>  filler7 char(255) not null)
    10> with fillfactor = 100
    11> go
    

    We use 7 char(255) fields to pad our small table. We also specify the fillfactor create table option to be 100. A fillfactor of 100 tells the server to completely fill every data page. Now, during your initial insertion of a line of data, do this:

    1> insert into keystorage
    2>   (tablename,lastkey,
    3>   filler1,filler2,filler3,filler4,filler5,filler6,filler7)
    4> values
    5>   ("yourtable",0,
    6>   replicate("x",250),replicate("x",250),
    7>   replicate("x",250),replicate("x",250),
    8>   replicate("x",250),replicate("x",250),
    9>   replicate("x",250))
    10> go
    

    This pads the row with 1750 bytes of junk, almost guaranteeing that, given a row's byte size limit of 1962 bytes (a row cannot span more than one page, thus the 2048 page size minus server overhead == 1962), we will be able to simulate row level locking.
  • In Sybase 11, a new create table option was introduced: max_rows_per_page. It automates the manual procedures above and guarantees at a system level what we need to achieve; one row per page.

    1> create table keystorage
    2> (tablename varchar(25),
    3>  lastkey int)
    4> with max_rows_per_page = 1
    5> go
    

• Create unique clustered indexes on the tablename/entity name within your keystorage table. This can only improve its performance. Remember to set max_rows_per_page or the fillfactor on your clustered index, as clustered indexes physically reorder the data.
• Break up the process into multiple transactions wherever possible; this will reduce the amount of time any table lock is held and will increase concurrency in high insertion environments.
• Use Stored Procedures: Put the SQL commands that update the keystorage table and then insert the updated key value into a stored procedure. Stored procedures are generally faster than individual SQL statements in your code because procedures are pre-compiled and have optimization plans for index usage stored in Sybase's system tables.
• Enhance the keystorage table to contain a fully qualified table name as opposed to just the tablename. This can be done by adding fields to the table defenition or by just expanding the entity name varchar field defenition. Then place the keystorage table in a central location/common database that applications share. This will eliminate muliple keystorage tables but might add length to queries (since you have to do cross-database queries to obtain the next key). Back to top