SQL Server: insert next available int

Question

I'm dealing with a table in SQL Server that has a serial_no column, which is defined as a non null int. It doesn't appear to be an auto incrementing field, as if I leave that column out of my insert statement I get an error saying that the serial_no field cannot be null.

So how do I insert the next available number?

I tried this:

INSERT INTO mytable (serial_no) VALUES ( (SELECT MAX(serial_no)+1 FROM mytable))

but I get an error saying that subqueries cannot be used in this context.

EDIT: This table is used in an off the shelf product so I cannot change the design and make the serial_no column an auto increment.

Answer 1

You can improve write concurrency with locking hints

INSERT INTO mytable (serial_no, value)
SELECT MAX (serial_no)+1, @value 
FROM mytable WITH (ROWLOCK, XLOCK, HOLDLOCK)

If performance is't important, try TABLOCKX in place of ROWLOCK, XLOCK

However, given this isn't safe either you need to retry

DECLARE @retry bit
SET @retry = 1

WHILE @Retry = 1
BEGIN
    BEGIN TRY
        INSERT INTO mytable (serial_no, value)
        SELECT MAX (serial_no)+1, @value 
        FROM mytable WITH (ROWLOCK, XLOCK, HOLDLOCK)

        SET @Retry = 0
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 2627  --PK violation
            RAISERROR ('blah', 16, 1)
    END CATCH
END

Or change to an IDENTITY column and do it properly...