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 + 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 enti
