有没有人有一个很好的方法来实现像SQL服务器中的序列？
有时候你只是不想使用GUID,除了它们是丑陋的事实.也许您想要的序列不是数字？此外,插入一行,然后询问数据库的数字是什么似乎是如此hackish.
Sql Server 2012引入了SEQUENCE对象,允许您生成与任何表无关的顺序数值.
创建它们很简单:
CREATE SEQUENCE Schema.SequenceName AS int INCREMENT BY 1 ;
插入前使用它们的示例:
DECLARE @NextID int ; SET @NextID = NEXT VALUE FOR Schema.SequenceName; -- Some work happens INSERT Schema.Orders (OrderID, Name, Qty) VALUES (@NextID, 'Rim', 2) ;
请参阅我的博客,深入了解如何使用序列:
http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance/
正如sqljunkieshare所说,从SQL Server 2012开始,有一个内置SEQUENCE功能.
原始问题没有澄清,但我认为序列的要求是:
它必须提供一组独特的增长数字
如果多个用户同时请求序列的下一个值,则所有用户都应获得不同的值.换句话说,无论如何,都保证生成值的唯一性.
由于某些事务可能被回滚的可能性,生成的数字的最终结果可能会有间隙.
我想对原始问题中的陈述发表评论:
"此外,插入一行,然后询问数据库这个数字看起来是多么的hackish."
嗯,我们在这里做的不多.DB是序列号的提供者,DB处理您自己无法处理的所有这些并发问题.我没有看到要求DB获取序列的下一个值的替代方法.必须有一个原子操作"给我序列的下一个值",只有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.
没有savepoint_name或transaction_name的ROLLBACK TRANSACTION回滚到事务的开头.嵌套事务时,此同一语句将所有内部事务回滚到最外面的BEGIN TRANSACTION语句.
这是我使用该过程的方法(在其他一些大程序中,例如,创建一个新的合同):
DECLARE @VarContractNumber int; EXEC @VarContractNumber = dbo.GetNewContractNumber;
如果您需要一次生成一个序列值,一切正常.在合同的情况下,每个合同都是单独创建的,因此这种方法非常有效.我可以肯定所有合同总是有唯一的合同号.
注意:只是为了防止可能的问题.这些合同号是我的Contracts表所具有的代理身份密钥的补充.代理键是用于引用完整性的内部键.生成的合同号是合同上印刷的人性化号码.此外,相同的合同表包含最终合同和提案,这些合同可以成为合同或永久保留为提案.提案和合同都包含非常相似的数据,这就是为什么它们保存在同一个表中.通过简单地改变一行中的标志,提案可以成为契约.提案使用单独的数字序列编号,我有第二个表SequenceProposalNumber和第二个程序GetNewProposalNumber.
不过,最近我遇到了一个问题.我需要批量生成序列值,而不是一个一个.
我需要一个程序来处理在一个特定季度内一次性收到的所有付款.这种处理的结果可能是我要在Transactions表中记录的约20,000个事务.我这里有类似的设计.Transactionstable具有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在事务中清理Sequence表.然后将生成的序列值从表变量复制@TableTransactionNumbers到临时表#NewS2TransactionNumbers,因为只有临时表#NewS2TransactionNumbers对存储过程的调用者可见.表变量@TableTransactionNumbers对存储过程的调用者不可见.
此外,可以使用OUTPUT子句将生成的序列直接发送给调用者(如您在使用的注释变体中所见MERGE).它本身工作正常,但我需要在某些表中生成的值,以便在调用存储过程中进一步处理.当我尝试这样的事情时:
INSERT INTO @TableTransactions (ID) EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;
我收到了一个错误
无法在INSERT-EXEC语句中使用ROLLBACK语句.
但是,我需要ROLLBACK内部EXEC,这就是为什么我最终有这么多临时表.
毕竟,切换到具有适当SEQUENCE对象的最新版SQL服务器有多好.
Identity列大致类似于序列.
您可以使用普通的旧表并将它们用作序列.这意味着您的插入始终是:
BEGIN TRANSACTION SELECT number from plain old table.. UPDATE plain old table, set the number to be the next number INSERT your row COMMIT
但是不要这样做.锁定会很糟糕......
我从SQL Server开始,对我而言,Oracle"序列"方案看起来像是一个黑客.我猜你是从另一个方向和你来的,而scope_identity()看起来像是一个黑客.
克服它.在罗马做到入乡随俗.
京公网安备 11010802040832号 | 京ICP备19059560号-6 