It is possible to avoid the dummy column, ever-growing number of rows in the hidden tables, and avoid locking issues by using an approach based on this example code: (the example just uses a single range, but it is easy to extend the idea)

USE tempdb;

GO

-- Drop the table if it exists from a previous run

IF OBJECT_ID(N'dbo.SneakyIdentity', N'U') IS NOT NULL

BEGIN

DROP TABLE dbo.SneakyIdentity;

END;

GO

-- Create the hidden table used to allocate IDs

CREATE TABLE dbo.SneakyIdentity (row_id BIGINT IDENTITY NOT NULL);

GO

-- The allocation routine

CREATE PROCEDURE dbo.Allocate

(

@ID BIGINT OUTPUT

)

AS

BEGIN

-- No DONE_IN_PROC messages

SET NOCOUNT ON

;

-- Almost all errors will abort the batch

SET XACT_ABORT ON

;

-- Table variable used with the OUTPUT clause

-- to safely read the identity value assigned.

-- Issues (bugs) have arisen in the past with

-- @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT.

DECLARE @Output TABLE

(row_id BIGINT NOT NULL)

;

-- Start error handler

BEGIN TRY

-- Might not be the top-level transaction!

BEGIN TRANSACTION;

-- Save point

SAVE TRANSACTION AllocateID;

-- Allocate an ID using the 'hidden' table

INSERT dbo.SneakyIdentity

OUTPUT inserted.row_id

INTO @Output (row_id)

DEFAULT VALUES;

-- Rollback to the save point

ROLLBACK TRANSACTION AllocateID;

-- Correct the transaction count

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Remember the error message

DECLARE @ErrMsg NVARCHAR(2048);

SET @ErrMsg = ERROR_MESSAGE();

-- Uncommittable transaction => unconditional rollback

-- (this is the only option)

IF XACT_STATE() = -1 ROLLBACK TRANSACTION;

-- If our transaction is still alive, roll back to

-- the save point, and adjust @@TRANCOUNT

IF XACT_STATE() = 1

BEGIN

-- Rollback to the save point

ROLLBACK TRANSACTION AllocateID;

-- Correct the transaction count

COMMIT TRANSACTION;

END

-- Custom error handling goes here

RAISERROR('Error in allocation: %s', 16, 1, @ErrMsg);

-- Error

RETURN 999;

END CATCH;

-- Capture assigned value

-- (Table variables are not affected by transaction rollbacks)

SET @ID =

(

SELECT TOP (1)

row_id

FROM @Output

);

-- Success

RETURN 0

;

END;

GO

-- === TEST ===

-- Variables

DECLARE @ID BIGINT,

@rc INTEGER;

;

-- Simulate an enclosing user transaction

BEGIN TRANSACTION

;

-- Allocate the next id

EXECUTE @rc =

dbo.Allocate @ID OUTPUT

;

-- Show locks

SELECT *

FROM sys.dm_tran_locks AS TL

WHERE TL.request_session_id = @@SPID

;

-- Show procedure return code and allocated ID

SELECT return_code = @rc,

allocated_id = @ID

;

-- Now roll back or commit

ROLLBACK

;

-- Uncomment this when finished

-- DROP TABLE dbo.SneakyIdentity;

-- DROP PROCEDURE dbo.Allocate;