SQL Server: insert next available int

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 ch开发者_StackOverflow社区ange the design and make the serial_no column an auto increment.

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...

The error can be fixed by dropping the VALUES

INSERT INTO mytable (serial_no, value)

SELECT MAX(serial_no)+1 ,
@value 
FROM mytable)

But this is a bad idea. There's a race condition on MAX(serial_no)+1 (e.g. two Inserts get the same value for Max(Serial_no).

You're better off using an auto increment field. You could also create a table that stores the current next value and increment it instead of using max.

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

Try it without VALUES:

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