More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How do I generate Sequential Keys w/o the Identity feature in Sybase?
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
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.
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
There is plenty of room for error checking with this process: for example
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
(Continued on next question...)
