Корректный способ генерировать номера заказа в SQL Server

Обычно я использую что-то вроде этого:

update OrderNumberInfo with (rowlock)
set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1
where VendorID = @VendorID

Это не нужно заключать в транзакцию. Фактически, если вы все-таки заключите его в транзакцию, SQL Server начнет удерживать блокировки на таблице и все замедлится. Когда мне нужно сделать что-то подобное в веб-сервисе, я всегда выполняю это в отдельном подключении к базе данных вне любой транзакции, которая может быть открыта в то время, просто для уверенности.

Я считаю (но не доказал), что SQL Server использует защелку, а не транзакцию, чтобы сделать ее атомарной, что должно быть более эффективным.

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

declare @error int, @rowcount int

-- Attempt to read and update the number.
update OrderNumberInfo with (rowlock)
set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1
where VendorID = @VendorID

select @error = @@error, @rowcount = @@rowcount
if @error <> 0 begin
    return @error
end

-- If the update succeeded then exit now.
if @rowcount > 0 begin
    return 0
end

-- Insert the row if it doesn't exist yet.
insert into OrderNumberInfo (VendorID, OrderNumber)
select VendorID, 1
where not exists (select null from OrderNumberInfo where VendorID = @VendorID)

select @error = @@error
if @error <> 0 begin
    return @error
end

-- Attempt to read and update the number.
update OrderNumberInfo with (rowlock)
set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1
where VendorID = @VendorID

select @error = @@error
if @error <> 0 begin
    return @error
end

Этот код по-прежнему не требует транзакции, потому что каждый атомарный оператор будет успешно, независимо от того, сколько других подключений выполняют код одновременно.

Заявление об ограничении ответственности: Я использовал это без проблем на SQL Server 7-2005. Я пока не могу комментировать его поведение в 2008 году.

Ваше решение создаст потенциальное узкое место для производительности в таблице OrderNumberInfo .

Есть ли какая-то конкретная причина, по которой заказы не могут быть просто столбцом идентификации, возможно, с префиксом идентификатора поставщика на стороне приложения (например, MSFT-232323)?

Единственный недостаток этого подхода заключается в том, что для каждого поставщика Заказы не будут шаблоном «Добавить-1-для-получения-следующего-заказа- #», но мне не известно о каких-либо технических или деловых соображениях, объясняющих, почему это может вызвать проблему, хотя это может привести к порядку в последовательности обработка немного сложнее.

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

Это, конечно, будет иметь дополнительное побочное преимущество в виде очень простой логики, не зависящей от поставщика (если она у вас когда-либо будет) - например, контроль качества / отчетность в масштабе всего приложения.

Вы можете использовать предложение OUTPUT . Это должно делать все атомарно, не требуя транзакции.

-- either return the order number directly as a single column resultset
UPDATE OrderNumberInfo 
SET OrderNumber = OrderNumber + 1
    OUTPUT DELETED.OrderNumber
WHERE VendorID = @vendorID


-- or use an intermediate table variable to get the order number into @n
DECLARE @n INT
DECLARE @temp TABLE ( OrderNumber INT )

UPDATE OrderNumberInfo 
SET OrderNumber = OrderNumber + 1
    OUTPUT DELETED.OrderNumber
    INTO @temp ( OrderNumber )
WHERE VendorID = @vendorID

SET @n = (SELECT TOP 1 OrderNumber FROM @temp)

В приведенных выше примерах предполагается, что столбец VendorID имеет уникальное ограничение или, по крайней мере, будет только одна строка для каждого идентификатора поставщика. Если это не так, вы потенциально будете обновлять и / или возвращать несколько строк, что не кажется хорошей идеей!