How to Set & Get the Next ID Without Serializable Isolation

Say for some reason you can’t use identity columns or sequences, or maybe your application was built a long, long time ago – and you’ve got a table with a list of IDs to use next:

DROP TABLE IF EXISTS dbo.Ids; CREATE TABLE dbo.Ids (TableName NVARCHAR(255) PRIMARY KEY CLUSTERED, CurrentId BIGINT); INSERT INTO dbo.Ids (TableName, CurrentId) VALUES (N’Customers’, 1000); GO

Whenever your application wants to insert a row, it needs to do two things: it needs to grab an ID for that table, and it needs to increment the CurrentID by one. This is a common design pattern I see with older