Generating surrogate key without IDENTITY

[John]

Hello
I'm looking for a way of generating the next key value that works in MS and Sybase SQL Servers. Sybase identity columns are a bit dodgy, so...

If I have a separate table NextKey (NextKey int) with one row that I update as follows...

declare @NextKey int
update NextKey set NextKey = NextKey + 1, @NextKey = NextKey + 1
insert into myTable (PrimaryKeyCol, ....) values (@NextKey, ....)

are there any problems with concurrency ? As I see it the update will lock the row so different connections will always come up with a different @NextKey value....

Thanks
John

I would do it in trigger anyway...



------------
John at 1/22/01 7:35:51 AM

Hello
I'm looking for a way of generating the next key value that works in MS and Sybase SQL Servers. Sybase identity columns are a bit dodgy, so...

If I have a separate table NextKey (NextKey int) with one row that I update as follows...

declare @NextKey int
update NextKey set NextKey = NextKey + 1, @NextKey = NextKey + 1
insert into myTable (PrimaryKeyCol, ....) values (@NextKey, ....)

are there any problems with concurrency ? As I see it the update will lock the row so different connections will always come up with a different @NextKey value....

Thanks
John

[John]

I thought an update trigger would only fire after a successful update - the ID value is the primary key so I need to get it at the start.

Even if this wasn't the case, inserts to this table only happen through one sp so it makes sense to put the logic there instead of having a trigger - I just want to find out the best way of generating the value....


------------
at 1/22/01 4:15:57 PM

I would do it in trigger anyway...



------------
John at 1/22/01 7:35:51 AM

Hello
I'm looking for a way of generating the next key value that works in MS and Sybase SQL Servers. Sybase identity columns are a bit dodgy, so...

If I have a separate table NextKey (NextKey int) with one row that I update as follows...

declare @NextKey int
update NextKey set NextKey = NextKey + 1, @NextKey = NextKey + 1
insert into myTable (PrimaryKeyCol, ....) values (@NextKey, ....)

are there any problems with concurrency ? As I see it the update will lock the row so different connections will always come up with a different @NextKey value....

Thanks
John