tsql - 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?

Answer 1

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)