如何在Microsoft SQL Server中实现序列?

SQL Server 2012已引入 SEQUENCE objects ,它允许您生成不与任何表关联的连续数值。

创建它们很容易:

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 功能特色

原来的问题并不清楚,但我认为序列的要求是:

1. 它必须提供一组独特的增长数。
2. 如果几个用户同时请求序列的下一个值,那么他们都应该得到不同的值。换言之,无论发生什么,都保证生成值的唯一性。
3. 由于某些事务可以回滚的可能性,生成的数字的最终结果可能会有差距。

我想对原问题中的陈述进行评论:

“此外,插入一行,然后问DB数字是什么 似乎很刻板。”

好吧,我们在这里无能为力。DB是序号的提供者,DB处理所有这些无法处理的并发问题。我认为除了向数据库询问序列的下一个值之外别无选择。必须有一个 原子的 操作“给我序列的下一个值”,只有db可以提供 原子的 操作。没有客户端代码可以保证只有他在处理序列。

要回答标题“您将如何实现序列”中的问题,我们使用的是2008,它没有 顺序 功能,所以在阅读了一些关于这个主题的内容后,我最终得到了以下内容。

对于我需要的每个序列,我创建一个单独的helper表,其中只有一个 IDENTITY 列(以与2012年相同的方式创建单独的序列对象)。

CREATE TABLE [dbo].[SequenceContractNumber]
(
    [ContractNumber] [int] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_SequenceContractNumber] PRIMARY KEY CLUSTERED ([ContractNumber] ASC)
)

可以指定起始值和增量。 然后创建一个存储过程,返回序列的下一个值。 过程将启动事务,将一行插入到帮助表中,记住生成的标识值并回滚事务。因此helper表始终为空。

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 是的。

无保存点名称或事务名称的回滚事务 回滚到事务的开头。嵌套时 事务中,此语句将所有内部事务回滚到 最外层的BEGIN TRANSACTION语句。

这就是我如何使用该过程(例如,在创建新合同的其他大型过程中):

DECLARE @VarContractNumber int;
EXEC @VarContractNumber = dbo.GetNewContractNumber;

如果你需要一次生成一个序列值,一切都很好。在合同的情况下,每个合同都是单独创建的,因此这种方法非常有效。我可以确定所有的合同都有唯一的合同号。

注:只是为了防止可能的问题。这些合同编号是对我的合同表所具有的代理标识密钥的补充。代理项密钥是用于引用完整性的内部密钥。生成的合同编号是打印在合同上的人性化编号。此外,同一个合同表包含最终合同和建议书,它们可以成为合同,也可以永远作为建议书保留。提案和合同都有非常相似的数据,这就是为什么它们被放在同一个表中的原因。只需在一行中更改标志,提案就可以成为合同。提案用一个单独的数字序列编号,我有第二张表 SequenceProposalNumber 第二程序 GetNewProposalNumber 是的。

不过,最近我遇到了一个问题。 我需要批量生成序列值,而不是逐个生成。

我需要一个程序,将处理在一个给定的季度内一次性收到的所有付款。这种处理的结果可能是大约20000个事务,我想在 Transactions 桌子。我这里也有类似的设计。 交易 表具有内部 身份 最终用户看不到的列,它有一个人性化的事务编号,将打印在语句中。所以,我需要一种方法在一个批处理中生成给定数量的唯一值。

从本质上讲,我使用了相同的方法,但没有什么特殊之处。

首先,没有直接的方法在一个只有一行的表中插入多行 身份 列。尽管有一个解决方法是(ab)使用 MERGE ,我最后没用。我觉得增加一个假人更容易 Filler 列。我的序列表总是空的,所以额外的列并不重要。

helper表如下所示:

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到100000的整数。它也用于系统中的其他地方。我检查是否有足够的行在 数字 表,并扩大到100000×100000通过交叉连接本身,如果需要的话。

我必须将大容量插入的结果保存在某个地方,并以某种方式传递给调用方。在存储过程之外传递表的一种方法是使用临时表。我不能在这里使用表值参数,因为它是只读的,很不幸。此外,我不能直接将生成的序列值插入到临时表中。 #NewS2TransactionNumbers 是的。我不能用 #新闻2交易编号 在 OUTPUT 子句,因为 回降 会清理干净的。幸运的是,表变量不受 回降 是的。

所以,我使用表变量 @TableTransactionNumbers 作为目的地 输出 条款。然后我 回降 清理序列表的事务。然后从表变量复制生成的序列值 @表格交易编号 到临时桌子 #新闻2交易编号 ,因为只有临时表 #新闻2交易编号 对存储过程的调用方可见。table变量 @表格交易编号 对存储过程的调用方不可见。

而且,它可以使用 输出 子句将生成的序列直接发送给调用方(如您在使用的注释变量中可以看到的)。 合并 )中。它本身运行良好,但我需要在一些表中生成的值,以便在调用存储过程中进行进一步处理。当我试着这样做的时候:

INSERT INTO @TableTransactions (ID)
EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;

我犯了个错误

不能在INSERT-EXEC语句中使用ROLLBACK语句。

但是,我需要 回降 在里面 EXEC ,所以我才有这么多临时桌子。

在所有这些之后,切换到最新版本的sql server会有多好 顺序 反对。

An Identity column 大致类似于一个序列。

您可以使用普通的旧表并将它们用作序列。这意味着您的插入内容将始终是:

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()看起来像个黑客。

克服它。在罗马的时候,要像罗马人一样。

我用来解决这个问题的方法是一个存储所有序列的表'sequences'和一个'nextval'存储过程。

SQL表:

CREATE TABLE Sequences (  
    name VARCHAR(30) NOT NULL,  
    value BIGINT DEFAULT 0 NOT NULL,  
    CONSTRAINT PK_Sequences PRIMARY KEY (name)  
);

这个 pk_序列 只是用来确保永远不会有同名的序列。

SQL存储过程:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'nextVal') AND type in (N'P', N'PC')) DROP PROCEDURE nextVal;  
GO  
CREATE PROCEDURE nextval  
    @name VARCHAR(30)  
AS  
    BEGIN  
        DECLARE @value BIGINT  
        BEGIN TRANSACTION  
            UPDATE Sequences  
            SET @value=value=value + 1  
            WHERE name = @name;  
            -- SELECT @value=value FROM Sequences WHERE name=@name  
        COMMIT TRANSACTION  
        SELECT @value AS nextval  
    END;  

插入一些序列:

INSERT INTO Sequences(name, value) VALUES ('SEQ_Workshop', 0);
INSERT INTO Sequences(name, value) VALUES ('SEQ_Participant', 0);
INSERT INTO Sequences(name, value) VALUES ('SEQ_Invoice', 0);  

最后得到序列的下一个值,

execute nextval 'SEQ_Participant';

一些C代码从序列表中获取下一个值,

public long getNextVal()
{
    long nextval = -1;
    SqlConnection connection = new SqlConnection("your connection string");
    try
    {
        //Connect and execute the select sql command.
        connection.Open();

        SqlCommand command = new SqlCommand("nextval", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "SEQ_Participant";
        nextval = Int64.Parse(command.ExecuteScalar().ToString());

        command.Dispose();
    }
    catch (Exception) { }
    finally
    {
        connection.Dispose();
    }
    return nextval;
}

在SQL Server 2012中,您可以简单地使用

CREATE SEQUENCE

在2005年和2008年,您可以使用公共表表达式获得序列号的任意列表。

下面是一个例子(注意maxrecursion选项很重要):

DECLARE @MinValue INT = 1;
DECLARE @MaxValue INT = 1000;

WITH IndexMaker (IndexNumber) AS
(
    SELECT 
        @MinValue AS IndexNumber
    UNION ALL SELECT 
        IndexNumber + 1
    FROM
        IndexMaker
    WHERE IndexNumber < @MaxValue
)
SELECT
    IndexNumber
FROM
    IndexMaker
ORDER BY
    IndexNumber
OPTION 
    (MAXRECURSION 0)

由Oracle实现的序列需要在插入之前调用数据库。 SQL Server实现的标识需要在插入之后调用数据库。

一个并不比另一个更老套。净效果是相同的-对数据存储区的依赖/依赖性,以提供唯一的人工密钥值,并且(在大多数情况下)两个调用到存储区。

我假设您的关系模型是基于人工键的,在这种情况下,我将提供以下观察:

我们永远不应该试图给人工密钥注入意义,它们的唯一目的应该是链接相关记录。

你对订购数据有什么要求?它是否可以在视图(演示文稿)中处理,或者是数据必须保留的真实属性吗?

创建一个带有标识符的stage表。

在加载stage表之前,截断标识符并重新设置其种子,使其从1开始。

把你的桌子搬上。现在每一行都有一个从1到n的唯一值。

创建一个保存序列号的表。这可以是几行,每个序列都有一行。

从您创建的序列表中查找序列号。 通过将stage表中的行数添加到序列号来更新sequence号。

通过添加查找到的序列号来更新阶段表标识符。这是一个简单的单步过程。 或 加载目标表,在ETL中加载时将序列号添加到标识符。这可以利用大容量加载程序并允许其他转换。

考虑下面的片段。

CREATE TABLE [SEQUENCE](
    [NAME] [varchar](100) NOT NULL,
    [NEXT_AVAILABLE_ID] [int] NOT NULL,
 CONSTRAINT [PK_SEQUENCES] PRIMARY KEY CLUSTERED 
(
    [NAME] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE CLAIM_IDS (@sequenceName varchar(100), @howMany int)
AS
BEGIN
    DECLARE @result int
    update SEQUENCE
        set
            @result = NEXT_AVAILABLE_ID,
            NEXT_AVAILABLE_ID = NEXT_AVAILABLE_ID + @howMany
        where Name = @sequenceName
    Select @result as AVAILABLE_ID
END
GO

作为 sqljunkiesshare states ,序列已添加到SQL Server 2012。下面是如何在gui中实现的。这相当于:

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

1. 在 对象资源管理器 ,展开 可编程性 文件夹
2. 在 可编程性 文件夹,右键单击 序列 文件夹如下所示:

3. 下划线是要更新以获取 与上面的sql语句等价,但是,我会考虑 根据你的需要改变这些(见下面的注释)。

笔记:

• 默认起始值、最小值和最大值由数据类型的范围决定,在这种情况下,数据类型是int。 See here for more data type ranges if you want to use something other than an int 是的。

• 很好的机会,你希望你的序列从1开始 可能也希望最小值为1。

我完全同意,去年做了一个项目。

我刚刚创建了一个表,其中包含序列名称、当前值和增量金额。

然后我创建了两个过程来添加和删除它们。和两个函数获取下一个,获取当前值。

如果要插入带有顺序键的数据,但不希望再次查询数据库以获取刚刚插入的键,我认为您只有两个选择:

1. 通过返回新插入的键值的存储过程执行插入
2. 实现序列客户端(以便在插入之前知道新的密钥)

如果我在做客户端密钥生成,我 爱 吉兹。我觉得他们很漂亮。

row["ID"] = Guid.NewGuid();

那条线应该在某个地方的跑车引擎盖上。

如果使用的是SQL Server 2005,则可以选择使用行号

标识列的另一个问题是,如果有多个表的序号需要唯一,则标识列不起作用。就像Corey Trager提到的,滚动自己的序列实现可能会带来一些锁定问题。

最直接的等价解决方案似乎是创建一个sql server表,其中包含一列标识,该标识代替了单独类型的“sequence”对象。例如,如果在Oracle中,一个序列中有两个表,例如dogs<--sequence object--gt;cats,则在SQL Server中,您将创建三个数据库对象,所有表都像dogs<--pets with identity column--gt;cats。您可以在pets表中插入一行,以获取通常使用nextval的序列号,然后在从用户那里获得实际类型的宠物后,按照通常的方式插入dogs或cats表。任何额外的公共列都可以从dogs/cats表移到pets supertype表中,结果是:1)每个序列号有一行;2)获取序列号时无法填充的任何列都需要有默认值;3)获取所有列都需要连接。

通过sql可以使用这种策略;

CREATE SEQUENCE [dbo].[SequenceFile]
AS int
START WITH 1
INCREMENT BY 1 ;

并读取此sql的唯一下一个值

SELECT NEXT VALUE FOR [dbo].[SequenceFile]

交易安全! 对于2012之前的sqlserver版本…(谢谢马特G。) 讨论中缺少的一点是交易安全。如果你从一个序列中得到一个数字,那么这个数字必须是唯一的,并且没有其他应用程序或代码能够得到这个数字。在我的例子中,我们经常从序列中提取唯一的数字,但是实际的事务可能会占用相当长的时间,所以我们不希望任何其他人在提交事务之前得到相同的数字。 我们需要模拟Oracle序列的行为 ,其中一个号码在提取时被保留。 我的解决方案是使用xp_cmdshell在数据库上获取单独的会话/事务,以便我们可以立即更新整个数据库的序列,甚至在事务完成之前。

--it is used like this:
-- use the sequence in either insert or select:
Insert into MyTable Values (NextVal('MySequence'), 'Foo');

SELECT NextVal('MySequence');

--you can make as many sequences as you want, by name:
SELECT NextVal('Mikes Other Sequence');

--or a blank sequence identifier
SELECT NextVal('');

解决方案需要一个表来保存使用的序列值,并且需要一个过程 创建第二个自主事务 以确保并发会话不会陷入混乱。您可以拥有任意数量的唯一序列,它们是按名称引用的。修改下面的示例代码以省略序列历史表上的请求用户和日期戳(用于审核),但我认为对于该示例而言,不太复杂。

  CREATE TABLE SequenceHolder(SeqName varchar(40), LastVal int);

GO
CREATE function NextVAL(@SEQname varchar(40))
returns int
as
begin
    declare @lastval int
    declare @barcode int;

    set @lastval = (SELECT max(LastVal) 
                      FROM SequenceHolder
                     WHERE SeqName = @SEQname);

    if @lastval is null set @lastval = 0

    set @barcode = @lastval + 1;

    --=========== USE xp_cmdshell TO INSERT AND COMMINT NOW, IN A SEPERATE TRANSACTION =============================
    DECLARE @sql varchar(4000)
    DECLARE @cmd varchar(4000)
    DECLARE @recorded int;

    SET @sql = 'INSERT INTO SequenceHolder(SeqName, LastVal) VALUES (''' + @SEQname + ''', ' + CAST(@barcode AS nvarchar(50)) + ') '
    SET @cmd = 'SQLCMD -S ' + @@servername +
              ' -d ' + db_name() + ' -Q "' + @sql + '"'
    EXEC master..xp_cmdshell @cmd, 'no_output'

    --===============================================================================================================

    -- once submitted, make sure our value actually stuck in the table
    set @recorded = (SELECT COUNT(*) 
                       FROM SequenceHolder
                      WHERE SeqName = @SEQname
                        AND LastVal = @barcode);

    --TRIGGER AN ERROR 
    IF (@recorded != 1)
        return cast('Barcode was not recorded in SequenceHolder, xp_cmdshell FAILED!! [' + @cmd +']' as int);

    return (@barcode)

end

GO

COMMIT;

现在,为了让这个过程正常工作,你需要启用一个程序集,有很多关于如何做的好的描述,这里是我在尝试工作时所做的个人笔记。基本思想是,您需要在SQLServer Surt中打开XPyCMDHELL是一个配置,并且需要将用户帐户设置为XPyCMDS壳命令将运行的帐户,它将访问数据库插入序列号并提交它。

--- LOOSEN SECURITY SO THAT xp_cmdshell will run 
---- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
---- To update the currently configured value for advanced options.
RECONFIGURE
GO
---- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
---- To update the currently configured value for this feature.
RECONFIGURE
GO

—-Run SQLServer Management Studio as Administrator,
—- Login as domain user, not sqlserver user.

--MAKE A DATABASE USER THAT HAS LOCAL or domain LOGIN! (not SQL server login)
--insure the account HAS PERMISSION TO ACCESS THE DATABASE IN QUESTION.  (UserMapping tab in User Properties in SQLServer)

—grant the following
GRANT EXECUTE on xp_cmdshell TO [domain\user] 

—- run the following:
EXEC sp_xp_cmdshell_proxy_account 'domain\user', 'pwd'

--alternative to the exec cmd above: 
create credential ##xp_cmdshell_proxy_account## with identity = 'domain\user', secret = 'pwd'


-—IF YOU NEED TO REMOVE THE CREDENTIAL USE THIS
EXEC sp_xp_cmdshell_proxy_account NULL;


-—ways to figure out which user is actually running the xp_cmdshell command.
exec xp_cmdshell 'whoami.exe'  
EXEC xp_cmdshell 'osql -E -Q"select suser_sname()"'
EXEC xp_cmdshell 'osql -E -Q"select * from sys.login_token"'