>>One possible but unlikely problem is that the key sequence is not integer-based.
True, can't use an identity here.
>> Another issue could be that if the key field is visible in the application, the client may want it to increase in random steps to give their competitors the impression they are doing well. This might sound far-fetched, but I actually had to do it for a company in London a few years ago.
Interesting. I've never run into this. I'm not sure that I'd let this single exception change the way that I globally develop software.
>> There are also implications of course if the table is being replicated.
I'm not sure what you're getting at here. I'm been involved with many projects where PKs were generated by identities and the data was replicated.
Anyway...then to your original question. Given a table such as:
CREATE TABLE pk (table nvarchar(128), nextkey int)
You can fetch the next key and increment the value in a single UPDATE statement:
DECLARE @nextkey int
UPDATE pk SET @nextkey = nextkey, nextkey = nextkey + 1 WHERE table = @table
The benefit of this approach is that SQL Server handles the locking for you.
-Mike