SQL Server - Auto-incrementation that allows UPDATE statements

Question

When adding an item in my database, I need it to auto-determine the value for the field DisplayOrder. Identity (auto-increment) would be an ideal solution, but I need to be able to programmatically change (UPDATE) the values of the DisplayOrder column, and Identity doesn't seem to allow that. For the moment, I use this code:

CREATE PROCEDURE [dbo].[AddItem]

AS

DECLARE @DisplayOrder INT

SET @DisplayOrder = (SELECT MAX(DisplayOrder) FROM [dbo].[MyTable]) + 1

INSERT INTO [dbo].[MyTable] ( DisplayOrder ) VALUES ( @DisplayOrder )

Is it the good way to do it or is there a better/simpler way?

Unfavorable

I just encountered a problem with this method: MAX returns NULL if there are no rows in the table. Therefore, this procedure can't add the first row since it generates an error! – Unfavorable

Found a fix here: #1689215 – Unfavorable

Answer 1

A solution to this issue from "Inside Microsoft SQL Server 2008: T-SQL Querying"

CREATE TABLE dbo.Sequence(
 val int IDENTITY (10000, 1) /*Seed this at whatever your current max value is*/
 )

GO

CREATE PROC dbo.GetSequence
@val AS int OUTPUT
AS
BEGIN TRAN
    SAVE TRAN S1
    INSERT INTO dbo.Sequence DEFAULT VALUES
    SET @val=SCOPE_IDENTITY()
    ROLLBACK TRAN S1 /*Rolls back just as far as the save point to prevent the 
                       sequence table filling up. The id allocated won't be reused*/
COMMIT TRAN

Or another alternative from the same book that allocates ranges easier. (You would need to consider whether to call this from inside or outside your transaction - inside would block other concurrent transactions until the first one commits)

CREATE TABLE dbo.Sequence2(
 val int 
 )

GO

INSERT INTO dbo.Sequence2 VALUES(10000);

GO

CREATE PROC dbo.GetSequence2
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequence2 
SET @val = val = val + @n;

SET @val = @val - @n + 1; 

Pneumatic

Answer 2

You can set your incrementing column to use the identity property. Then, in processes that need to insert values into the column you can use the SET IDENITY_INSERT command in your batch.

For inserts where you want to use the identity property, you exclude the identity column from the list of columns in your insert statement:

INSERT INTO [dbo].[MyTable] ( MyData ) VALUES ( @MyData )

When you want to insert rows where you are providing the value for the identity column, use the following:

SET IDENTITY_INSERT MyTable ON

INSERT INTO [dbo].[MyTable] ( DisplayOrder, MyData )
VALUES ( @DisplayOrder, @MyData )

SET IDENTITY_INSERT MyTable OFF

You should be able to UPDATE the column without any other steps.

You may also want to look into the DBCC CHECKIDENT command. This command will set your next identity value. If you are inserting rows where the next identity value might not be appropriate, you can use the command to set a new value.

DECLARE @DisplayOrder INT

SET @DisplayOrder = (SELECT MAX(DisplayOrder) FROM [dbo].[MyTable]) + 1

DBCC CHECKIDENT (MyTable, RESEED, @DisplayOrder)

Gatepost

(BTW there's a typo in SET IDENITY_INSERT) – Unfavorable

It's a pleasure to help. I'll correct the typo for future readers. Good luck. – Gatepost

I just noticed that IDENTITY_INSERT doesn't seem to allow UPDATE statements on identity columns (it only allows INSERT statements). I will need another solution. – Unfavorable

Answer 3

Here's the solution that I kept:

CREATE PROCEDURE [dbo].[AddItem]

AS

DECLARE @DisplayOrder INT

BEGIN TRANSACTION

SET @DisplayOrder = (SELECT ISNULL(MAX(DisplayOrder), 0) FROM [dbo].[MyTable]) + 1

INSERT INTO [dbo].[MyTable] ( DisplayOrder ) VALUES ( @DisplayOrder )

COMMIT TRANSACTION

Unfavorable

This can cause duplicate DisplayOrders to be entered if you have concurrent executions of AddItem – Pneumatic

But the SELECT and INSERT statements are enclosed in a transaction. Isn't it supposed to fix duplicates? – Unfavorable

I think that was the idea but it wouldn't work without additional locking hints under any isolation level. As the two concurrent selects wouldn't block each other and would both read the same value. The inserts would then succeed except for at serializable level when I think you would get deadlock. – Pneumatic

Answer 4

One thing you should do is to add commands so that your procedure's run as a transaction, otherwise two inserts running at the same time could produce two rows with the same value in DisplayOrder.

This is easy enough to achieve: add

begin transaction

at the start of your procedure, and

commit transaction

at the end.

Adur

This answer doesn't seem to answer the question. – Gatepost

It answers the bit of the question as to whether the proposed way could be improved upon. – Adur

Wrapping the SELECT and INSERT in a transaction does not block 2 concurrent selects reading the max value – Pneumatic

Answer 5

You way works fine (with a little modification) and is simple. I would wrap it in a transaction like @David Knell said. This would result in code like:

CREATE PROCEDURE [dbo].[AddItem]

AS

DECLARE @DisplayOrder INT

BEGIN TRANSACTION

SET @DisplayOrder = (SELECT MAX(DisplayOrder) FROM [dbo].[MyTable]) + 1

INSERT INTO [dbo].[MyTable] ( DisplayOrder ) VALUES ( @DisplayOrder )

COMMIT TRANSACTION

Wrapping your SELECT & INSERT in a transaction guarantees that your DisplayOrder values won't be duplicated by AddItem. If you are doing a lot of simultaneous adding (many per second), there may be contention on MyTable but for occasional inserts it won't be a problem.

Achromic

Wrapping the SELECT and INSERT in a transaction does not guarantee this at all. – Pneumatic

You are correct, I think this answer actually has a much cleaner solution: #193757 – Achromic

Yep. A version of that was in the book referenced in my answer as well. It handles allocation of chunks of numbers a lot easier than the solution I chose. The thing with that solution is that it if the sequence number is allocated from inside the same transaction it will block any concurrent inserts until the first transaction has finished which may or may not not be desirable. The identity table solution is non blocking. Edit: having looked at it again it's not at all the same. I'll update my answer with the alternative one. – Pneumatic