SQL Server — автоматическое увеличение, позволяющее выполнять операторы UPDATE.

Решение этой проблемы из статьи «Внутри Microsoft SQL Server 2008: запросы T-SQL».

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

Или другая альтернатива из той же книги, которая упрощает выделение диапазонов. (Вам нужно будет подумать, вызывать ли это изнутри или снаружи вашей транзакции - внутри будут блокироваться другие параллельные транзакции до тех пор, пока первая не зафиксирует)

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; 

Вы можете настроить свой увеличивающийся столбец на использование свойства identity. Затем в процессах, которым необходимо вставить значения в столбец, вы можете использовать команду SET IDENITY_INSERT в своем пакете.

Для вставок, где вы хотите использовать свойство удостоверения, вы исключаете столбец идентичности из списка столбцов в инструкции вставки:

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

Если вы хотите вставить строки, где вы предоставляете значение для столбца идентификаторов, используйте следующее:

SET IDENTITY_INSERT MyTable ON

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

SET IDENTITY_INSERT MyTable OFF

Вы должны иметь возможность ОБНОВИТЬ столбец без каких-либо других шагов.

Вы также можете изучить команду DBCC CHECKIDENT. Эта команда установит ваше следующее значение идентификатора. Если вы вставляете строки, где следующее значение идентификатора может оказаться неподходящим, вы можете использовать команду для установки нового значения.

DECLARE @DisplayOrder INT

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

DBCC CHECKIDENT (MyTable, RESEED, @DisplayOrder)

Вот решение, которое я сохранил:

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

Одна вещь, которую вы должны сделать, это добавить команды, чтобы ваша процедура выполнялась как транзакция, иначе две вставки, выполняемые одновременно, могут создать две строки с одним и тем же значением в DisplayOrder.

Этого достаточно легко добиться: добавьте

begin transaction

в начале процедуры и

commit transaction

в конце.

Ваш способ отлично работает (с небольшой модификацией) и прост. Я бы обернул это транзакцией, как сказал @David Knell. Это приведет к коду вроде:

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

Включение SELECT и INSERT в транзакцию гарантирует, что ваши значения DisplayOrder не будут дублироваться AddItem. Если вы выполняете много одновременных добавлений (много раз в секунду), может возникнуть конфликт в MyTable, но для случайных вставок это не будет проблемой.