Thank you for the feedback

Agreed, the IDENTITY column works fine for creating a unique RECNUM ID, but this type of process is required when creating a 'unique' sequential number that will link multiple records, possible in multiple tables, such as an INVOICE, EVENT or DOCUMENT number.

Really my question is about when one is obliged to use this technique, what is the optimum way to do it to maximise concurrency performance and avoid deadlocks. ie minimise disk activity and lock time. Using a separate table for each entity is a bit clunky because it would create a large record set that would need regular truncation.

In summary:

What are the relative merits of handling the transaction ISOLATION LEVEL and/or the TABLOCKX, HOLDLOCK query Optimiser Hint at the SP level? Are they the same thing?

Best wishes,

Peter