From NOT a DBA ...

sounds like what you really need is an Oracle "sequence". I have implemented similar as follows:

CREATE PROCEDURE dbo.nextval @sequence varchar(28) AS

SET NOCOUNT ON

DECLARE @sql nvarchar(100), @scope_identity int

SET @sql = 'INSERT ' + @sequence + ' default values SELECT @scope_identity=SCOPE_IDENTITY()'

BEGIN TRANSACTION NEXTVAL

SAVE TRANSACTION SEQUENCER

EXEC sp_executesql @sql, N'@scope_identity INTEGER OUT', @scope_identity OUT

ROLLBACK TRANSACTION SEQUENCER

COMMIT TRANSACTION NEXTVAL

SET NOCOUNT OFF

SELECT @scope_identity;

GO

with a created table (@sequence) that contains only an identity column, this concept allows any number of "sequences".

The upside: locking is minimized or eliminated, the table size never changes (more or less)

The downside: there maybe gaps in the sequence

SQL Server has this "useful" feature, it never rolls back an identity value assigned, so even though the transaction is rolled back the identity value is not 🙂

Phil