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:
1> set identity_insert [datababase]..[table] on 2> goNote however that the System will not verify the uniqueness of the value you specifically insert (unless of course you have a unique index existing on the identity column).
% bcp [database]..[new_table] in [bcp datafile] -Usa -S[server] -f [fmt file] -EFor procedures on resetting indentity values during a bcp, see the section regarding Identity gaps.
1> sp_dboption [dbname], "auto identity", true 2> go or 1> sp_dboption [dbname], "identity in nonunique index", true 2> goTo tune the size of the auto identity (it defaults to precision 10):
1> sp_configure "size of auto identity", [desired_precision] 2> go(the identity in nonunique index db_option and the size of auto identity sp_configure value are new with System 11: the auto identity existed with the original Identity feature introduction in System 10)
The mechanism that Sybase uses to allocate Identities involves a memory based prefetch scheme for performance. The downside of this is, during non-normal shutdowns of the SQL server (shutdown with nowait or flat out crashes) the SQL server will simply discard or "burn" all the unused indentity values it has pre-allocated in memory. This sometimes leaves large "gaps" in your monotonically increasing identity columns and can be unsettling for some application developers and/or end users.
NOTE: Sybase 11.02.1 (EBF 6717) and below had a bug (bugid 96089) which would cause "large gaps to occur in identity fields after polite shutdowns." The Sybase 11.02.2 rollup (EBF 6886) fixed this problem. If you're at or below 11.02.1 and you use identities, you should defenitely upgrade.
NOTE: there are several other System 11 bugs related to Identities. 11.0.1 had a bug #83391 which caused the @@identity value to always be 0 (fixed in rollup 11.0.2). EBF 7312 fixes BugId 97748, which caused duplicate identity values to be inserted at times. EBF 6886 fixed (in addition to the above described bug) an odd bug (#82460) which caused a server crash when bcping into a table w/ an identity added via alter table. As always, try to stay current on EBFs.
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:
1> create table ident_test 2> (text_field varchar(10), 3> ident_field numeric(5,0) identity) 4> goOr alter an existing table and add an identity column:
1> alter table existing_table 2> add new_identity_field numeric(7,0) identity 3> goWhen 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).
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> goThis 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.
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)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% 100So, 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 top
1> sp_configure "identity grab size", [number] 2> goYou 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 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.
% bcp database..table out [data_file] -Usa -S[server] -NThis 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.
% bcp database.table in [data_file -Usa -S[server] -f[fmt file] -NThe -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.)
1> select [all columns except identity column] 2> [identity column name ] = identity(desired_precision) 3> into [new_table] 4> from [old table] 5> go
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
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> goThis 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.
1> create table keystorage 2> (tablename varchar(25), 4> lastkey int) 5> goAnd 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> goNow, 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> goMost 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 goAnd 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 (firstname.lastname@example.org) 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> goThis 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:
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> goWe 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> goThis 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.
1> create table keystorage 2> (tablename varchar(25), 3> lastkey int) 4> with max_rows_per_page = 1 5> go