Как правильно сказано в sqljunkieshare, начиная с SQL Server 2012 существует встроенный _ 1_.
Исходный вопрос не проясняет, но я предполагаю, что требования к Последовательности следующие:
- Он должен предоставлять набор уникальных растущих чисел.
- Если несколько пользователей одновременно запрашивают следующее значение последовательности, все они должны получить разные значения. Другими словами, уникальность сгенерированных значений гарантируется несмотря ни на что.
- Из-за возможности отката некоторых транзакций возможно, что конечный результат сгенерированных чисел будет иметь пропуски.
Я хотел бы прокомментировать утверждение в исходном вопросе:
«Кроме того, вставка строки и последующий запрос у БД, какое число кажется таким хакерским».
Что ж, здесь мы мало что можем с этим поделать. БД является поставщиком последовательных номеров, а БД решает все эти проблемы параллелизма, с которыми вы не можете справиться самостоятельно. Я не вижу альтернативы тому, чтобы запрашивать у БД следующее значение последовательности. Должна быть атомарная операция "дать мне следующее значение последовательности", и только DB может предоставить такую атомарную операцию. Никакой клиентский код не может гарантировать, что он единственный, кто работает с последовательностью.
Чтобы ответить на вопрос в заголовке «Как бы вы реализовали последовательности?» - мы используем 2008 год, в котором нет функции SEQUENCE
, поэтому после некоторого чтения по этой теме я пришел к следующему.
Для каждой нужной мне последовательности я создаю отдельную вспомогательную таблицу всего с одним столбцом IDENTITY
(так же, как в 2012 году, вы должны создать отдельный объект Sequence).
CREATE TABLE [dbo].[SequenceContractNumber]
(
[ContractNumber] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_SequenceContractNumber] PRIMARY KEY CLUSTERED ([ContractNumber] ASC)
)
Вы можете указать начальное значение и приращение для него. Затем я создаю хранимую процедуру, которая вернет следующее значение последовательности. Процедура запускает транзакцию, вставляет строку во вспомогательную таблицу, запоминает сгенерированное значение идентификатора и откатывает транзакцию. Таким образом, вспомогательная таблица всегда остается пустой.
CREATE PROCEDURE [dbo].[GetNewContractNumber]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @Result int = 0;
IF @@TRANCOUNT > 0
BEGIN
-- Procedure is called when there is an active transaction.
-- Create a named savepoint
-- to be able to roll back only the work done in the procedure.
SAVE TRANSACTION ProcedureGetNewContractNumber;
END ELSE BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION ProcedureGetNewContractNumber;
END;
INSERT INTO dbo.SequenceContractNumber DEFAULT VALUES;
SET @Result = SCOPE_IDENTITY();
-- Rollback to a named savepoint or named transaction
ROLLBACK TRANSACTION ProcedureGetNewContractNumber;
RETURN @Result;
END
Несколько замечаний о процедуре.
Во-первых, было неочевидно, как вставить строку в таблицу, в которой есть только один столбец идентификаторов. Ответ DEFAULT VALUES
.
Затем я хотел, чтобы процедура работала правильно, если она вызывается внутри другой транзакции. Простой ROLLBACK
откатывает все, если есть вложенные транзакции. В моем случае мне нужно откатить только INSERT
в вспомогательную таблицу, поэтому я использовал SAVE TRANSACTION
.
ROLLBACK TRANSACTION без имени точки сохранения или имени транзакции выполняет откат к началу транзакции. При вложении транзакций этот же оператор откатывает все внутренние транзакции до самого внешнего оператора BEGIN TRANSACTION.
Вот как я использую процедуру (внутри какой-то другой большой процедуры, которая, например, создает новый контракт):
DECLARE @VarContractNumber int;
EXEC @VarContractNumber = dbo.GetNewContractNumber;
Все это отлично работает, если вам нужно генерировать значения последовательности по одному. В случае контрактов каждый контракт создается индивидуально, поэтому такой подход отлично работает. Я могу быть уверен, что у всех контрактов всегда есть уникальные номера контрактов.
NB: Просто для предотвращения возможных вопросов. Эти номера контрактов дополняют суррогатный идентификационный ключ, который есть в моей таблице контрактов. Суррогатный ключ - это внутренний ключ, который используется для ссылочной целостности. Сгенерированный номер контракта - это понятный для человека номер, который печатается на контракте. Кроме того, одна и та же таблица контрактов содержит как окончательные контракты, так и предложения, которые могут стать контрактами или могут оставаться предложениями навсегда. И предложения, и контракты содержат очень похожие данные, поэтому хранятся в одной таблице. Предложение может стать контрактом, просто изменив флаг в одной строке. Предложения нумеруются с помощью отдельной последовательности чисел, для которой у меня есть вторая таблица SequenceProposalNumber
и вторая процедура GetNewProposalNumber
.
Однако недавно я столкнулся с проблемой. Мне нужно было генерировать значения последовательности в пакетном режиме, а не по одному.
Мне нужна процедура, которая обрабатывала бы все платежи, полученные в течение данного квартала, за один раз. Результатом такой обработки может быть ~ 20 000 транзакций, которые я хочу записать в таблицу Transactions
. У меня здесь похожий дизайн. Transactions
таблица имеет внутренний IDENTITY
столбец, который конечный пользователь никогда не видит, и имеет удобный для человека номер транзакции, который будет напечатан в операторе. Итак, мне нужен способ сгенерировать заданное количество уникальных значений в пакете.
По сути, я использовал тот же подход, но с некоторыми особенностями.
Во-первых, нет прямого способа вставить несколько строк в таблицу с одним IDENTITY
столбцом. Хотя есть обходной путь (ab) с помощью MERGE
, В конце концов, я им не воспользовался. Решил, что проще добавить фиктивный Filler
столбец. Моя таблица последовательности всегда будет пустой, поэтому дополнительный столбец не имеет значения.
Таблица помощников выглядит так:
CREATE TABLE [dbo].[SequenceS2TransactionNumber]
(
[S2TransactionNumber] [int] IDENTITY(1,1) NOT NULL,
[Filler] [int] NULL,
CONSTRAINT [PK_SequenceS2TransactionNumber]
PRIMARY KEY CLUSTERED ([S2TransactionNumber] ASC)
)
Порядок действий выглядит так:
-- Description: Returns a list of new unique S2 Transaction numbers of the given size
-- The caller should create a temp table #NewS2TransactionNumbers,
-- which would hold the result
CREATE PROCEDURE [dbo].[GetNewS2TransactionNumbers]
@ParamCount int -- not NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @@TRANCOUNT > 0
BEGIN
-- Procedure is called when there is an active transaction.
-- Create a named savepoint
-- to be able to roll back only the work done in the procedure.
SAVE TRANSACTION ProcedureGetNewS2TransactionNos;
END ELSE BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION ProcedureGetNewS2TransactionNos;
END;
DECLARE @VarNumberCount int;
SET @VarNumberCount =
(
SELECT TOP(1) dbo.Numbers.Number
FROM dbo.Numbers
ORDER BY dbo.Numbers.Number DESC
);
-- table variable is not affected by the ROLLBACK, so use it for temporary storage
DECLARE @TableTransactionNumbers table
(
ID int NOT NULL
);
IF @VarNumberCount >= @ParamCount
BEGIN
-- the Numbers table is large enough to provide the given number of rows
INSERT INTO dbo.SequenceS2TransactionNumber
(Filler)
OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID)
-- save generated unique numbers into a table variable first
SELECT TOP(@ParamCount) dbo.Numbers.Number
FROM dbo.Numbers
OPTION (MAXDOP 1);
END ELSE BEGIN
-- the Numbers table is not large enough to provide the given number of rows
-- expand the Numbers table by cross joining it with itself
INSERT INTO dbo.SequenceS2TransactionNumber
(Filler)
OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID)
-- save generated unique numbers into a table variable first
SELECT TOP(@ParamCount) n1.Number
FROM dbo.Numbers AS n1 CROSS JOIN dbo.Numbers AS n2
OPTION (MAXDOP 1);
END;
/*
-- this method can be used if the SequenceS2TransactionNumber
-- had only one identity column
MERGE INTO dbo.SequenceS2TransactionNumber
USING
(
SELECT *
FROM dbo.Numbers
WHERE dbo.Numbers.Number <= @ParamCount
) AS T
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT DEFAULT VALUES
OUTPUT inserted.S2TransactionNumber
-- return generated unique numbers directly to the caller
;
*/
-- Rollback to a named savepoint or named transaction
ROLLBACK TRANSACTION ProcedureGetNewS2TransactionNos;
IF object_id('tempdb..#NewS2TransactionNumbers') IS NOT NULL
BEGIN
INSERT INTO #NewS2TransactionNumbers (ID)
SELECT TT.ID FROM @TableTransactionNumbers AS TT;
END
END
И вот как он используется (внутри какой-то большой хранимой процедуры, вычисляющей транзакции):
-- Generate a batch of new unique transaction numbers
-- and store them in #NewS2TransactionNumbers
DECLARE @VarTransactionCount int;
SET @VarTransactionCount = ...
CREATE TABLE #NewS2TransactionNumbers(ID int NOT NULL);
EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;
-- use the generated numbers...
SELECT ID FROM #NewS2TransactionNumbers AS TT;
Здесь есть несколько вещей, требующих пояснения.
Мне нужно вставить заданное количество строк в SequenceS2TransactionNumber
таблицу. Я использую для этого вспомогательную Numbers
таблицу. В этой таблице просто хранятся целые числа от 1 до 100 000. Он также используется в других местах системы. Я проверяю, достаточно ли строк в таблице Numbers
, и при необходимости расширяю ее до 100 000 * 100 000 путем перекрестного соединения с самим собой.
Мне нужно где-то сохранить результат массовой вставки и как-то передать его вызывающей стороне. Один из способов передать таблицу за пределы хранимой процедуры - использовать временную таблицу. Я не могу использовать здесь возвращающий табличное значение параметр, потому что, к сожалению, он доступен только для чтения. Кроме того, я не могу напрямую вставить сгенерированные значения последовательности во временную таблицу #NewS2TransactionNumbers
. Я не могу использовать #NewS2TransactionNumbers
в предложении OUTPUT
, потому что ROLLBACK
очистит его. К счастью, переменные таблицы не зависят от ROLLBACK
.
Итак, я использую табличную переменную @TableTransactionNumbers
как место назначения предложения OUTPUT
. Затем я ROLLBACK
транзакцию, чтобы очистить таблицу последовательности. Затем скопируйте сгенерированные значения последовательности из табличной переменной @TableTransactionNumbers
во временную таблицу #NewS2TransactionNumbers
, потому что только временная таблица #NewS2TransactionNumbers
может быть видна вызывающему хранимую процедуру. Табличная переменная @TableTransactionNumbers
не видна вызывающей стороне хранимой процедуры.
Кроме того, можно использовать предложение OUTPUT
для отправки сгенерированной последовательности непосредственно вызывающей стороне (как вы можете видеть в закомментированном варианте, в котором используется MERGE
). Он отлично работает сам по себе, но мне нужны были сгенерированные значения в какой-то таблице для дальнейшей обработки в вызывающей хранимой процедуре. Когда я пробовал что-то вроде этого:
INSERT INTO @TableTransactions (ID)
EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;
Я получал ошибку
Невозможно использовать оператор ROLLBACK в операторе INSERT-EXEC.
Но мне нужно ROLLBACK
внутри EXEC
, поэтому у меня оказалось так много временных таблиц.
После всего этого, как хорошо было бы переключиться на последнюю версию SQL-сервера, у которой есть подходящий объект SEQUENCE
.
person
Vladimir Baranov
schedule
10.12.2014