Wow, I stared at this code for a good 30 minutes, just admiring the beauty and cleverness and (unsuccessfully) trying to find a flaw with it. I'm sad that I'm discovering this 2 years late, but this is a really cool (and yes, sneaky) trick. Paul, have you ever made a separate blog post detailing this technique? (Or know of a similar one by someone else?)

I don't have anything new to add, but want to rehash some of the things going on just to clarify for anybody that's confused or missing the point.

The OP's original problem was that he's using a stored procedure to get a key value from another table and seeing blocking because related calls to that stored procedure are trying to access a value from the same row. Note that even if the stored procedure itself doesn't leave an open transaction locking the rows, it's possible that the calling routine, which the DBA may or may not have control over, is calling the key procedure inside of a long running transaction, leaving long-standing locks in place despite the speed or intentions of the actual sproc. The OP's solution to this is to ensure that, rather than having parallel processes calling the sproc accessing the same row (which leads to the potential blocking), each call will create a new row in the table so that as long as only row locks are used, multiple calls will never block one another. The primary drawbacks of this are the potential need for many tables and the creation of many rows of data which are never used after their initial creation.

Paul's code doesn't directly address the problem of multiple different applications, although one could easily do the same as the OP and create a new table for each application and still use Paul's trick. One of the cool things about Paul's technique though is that it doesn't result in many rows... in fact, it doesn't result in ANY rows! He's essentially using the table's meta-data to track the keys rather than using the table data itself. And, he's doing it without ANY locking issues at all. (Yes, there's probably an instantaneous lock, but it won't get held even in a long-running transaction.)

So what's going on in Paul's code that makes it so sneaky? There's a few things, so I'll start from the middle and work out. The heart of it is the INSERT, which uses an OUTPUT clause to save the new IDENTITY value automatically generated by the table (note that, as others have posted, this OUTPUT technique is safer than using SCOPE_IDENTITY() or related functions). The OUTPUT saves the value in a table variable which is critical because table variables do not participate in transactions.

This becomes important because the INSERT is nested inside a SAVE/ROLLBACK transaction. I'm guessing everybody who reads this is fully aware of BEGIN/COMMIT/ROLLBACK transactions, although I do find that many people don't realize how nested transactions work. Transactions can be named to help clarify nesting levels, however, these names have NO effect on COMMIT/ROLLBACK calls. COMMIT will always commit the innermost transaction, while ROLLBACK will always rollback the outermost transaction, including all nested transactions. The exception to this is when creating a SAVE point, as Paul does. A ROLLBACK can not be limited to a nested BEGIN TRANSACTION, but it can be limited to a SAVE TRANSACTION point.

So what's happening here is Paul creates a SAVE point right before the insert, then inserts the data, and then immediately rolls back to the save point. The ROLLBACK undoes the INSERT, which includes releasing any locks that were allocated from the INSERT. However, because table variables are not included in transactions, the IDENTITY value which was OUTPUT into the table variable is still present and available for use. Also, the IDENTITY SEED value from the table's meta data isn't included in the transaction and so stays incremented even through the rollback, so that future calls will continue incrementing it rather than repeating values.

So the net result here is that, because of the save point and rollback, no rows are written to any table and no locks are being allocated. However, we still get back the new identity value, and the table meta data still has a record of what that newest identity value was so that it doesn't repeat. Sneaky indeed.

It's also worth paying attention to how Paul handles the TRY...CATCH block and his COMMIT transactions. Though not directly related to the intent of the post, they're often misunderstood and this is a great example of how to use them properly.

Sorry for the wordiness, but hope this helps clarify things.