Unique Incremental Number for Each Inserted Row without Using an IDENTITY Column

If you need to generate a unique incremental number for each inserted row without using an IDENTITY column, you must protect the MAX() value lookup inside a transaction. This prevents concurrent sessions from reading the same maximum value and inserting duplicates.

A simple approach is to lock the row(s) during the read using explicit locking hints:

DECLARE @Number DECIMAL(18,2);

BEGIN TRAN;

    SELECT @Number = MAX(Number)
    FROM Orders WITH (XLOCK, ROWLOCK);

    SET @Number = @Number + 1;

    INSERT INTO Orders (Number)
    VALUES (@Number);

COMMIT;

Tested with SQL Query Test: GitHub – ErikEJ/SqlQueryStress: SQL query stress simulator created by Adam Machanic http://dataeducation.com/sqlquerystress-the-source-code/ · GitHub