您将如何在 Microsoft SQL Server 中实现序列？

Question

提问by Nathan Lee

Does anyone have a good way of implementing something like a sequence in SQL server?

有没有人有在 SQL Server 中实现类似序列的好方法？

Sometimes you just don't want to use a GUID, besides the fact that they are ugly as heck. Maybe the sequence you want isn't numeric? Besides, inserting a row and then asking the DB what the number is just seems so hackish.

有时你只是不想使用 GUID，除了它们很丑的事实。也许您想要的序列不是数字？此外，插入一行然后询问 DB 数字是什么似乎太黑了。

Answer 1

回答by sqljunkieshare

Sql Server 2012 has introduced SEQUENCEobjects, which allow you to generate sequential numeric values not associated with any table.

Sql Server 2012 引入了SEQUENCEobjects，它允许您生成与任何表无关的顺序数值。

Creating them are easy:

创建它们很容易：

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

An example of using them before insertion:

插入前使用它们的示例：

DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Schema.SequenceName;
-- Some work happens
INSERT Schema.Orders (OrderID, Name, Qty)
  VALUES (@NextID, 'Rim', 2) ;

See my blog for an in-depth look at how to use sequences:

请参阅我的博客以深入了解如何使用序列：

http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance/

Answer 2

回答by Vladimir Baranov

As sqljunkiesharecorrectly said, starting from SQL Server 2012 there is a built-in SEQUENCEfeature.

正如sqljunkieshare正确所说，从 SQL Server 2012 开始有一个内置SEQUENCE功能。

The original question doesn't clarify, but I assume that requirements for the Sequence are:

最初的问题没有澄清，但我认为对 Sequence 的要求是：

It has to provide a set of unique growing numbers
If several users request next value of the sequence simultaneously they all should get different values. In other words, uniqueness of generated values is guaranteed no matter what.
Because of possibility that some transactions can be rolled back it is possible that end result of generated numbers will have gaps.

它必须提供一组独特的增长数字
如果多个用户同时请求序列的下一个值，他们都应该得到不同的值。换句话说，无论如何都保证生成值的唯一性。
由于某些交易可以回滚的可能性，因此生成的数字的最终结果可能会有差距。

I'd like to comment the statement in the original question:

我想评论原始问题中的声明：

"Besides, inserting a row and then asking the DB what the number just seems so hackish."

“此外，插入一行然后询问数据库这个数字看起来如此骇人听闻。”

Well, there is not much we can do about it here. The DB is a provider of the sequential numbers and DB handles all these concurrency issues that you can't handle yourself. I don't see alternative to asking the DB for the next value of the sequence. There has to be an atomicoperation "give me the next value of the sequence" and only DB can provide such atomicoperation. No client code can guarantee that he is the only one working with the sequence.

好吧，我们对此无能为力。DB 是序列号的提供者，DB 处理所有这些您自己无法处理的并发问题。我没有看到除了向数据库询问序列的下一个值之外的其他选择。必须有一个原子操作“给我序列的下一个值”，只有 DB 可以提供这样的原子操作。没有客户端代码可以保证他是唯一一个处理序列的人。

To answer the question in the title "how would you implement sequences" - We are using 2008, which doesn't have the SEQUENCEfeature, so after some reading on this topic I ended up with the following.

回答标题中的问题“您将如何实现序列” - 我们使用的是 2008，它没有该SEQUENCE功能，因此在阅读了有关此主题的一些内容后，我最终得到了以下内容。

For each sequence that I need I create a separate helper table with just one IDENTITYcolumn (in the same fashion as in 2012 you would create a separate Sequence object).

对于我需要的每个序列，我创建一个单独的辅助表，只有一IDENTITY列（以与 2012 年相同的方式，您将创建一个单独的 Sequence 对象）。

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

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

You can specify starting value and increment for it. Then I create a stored procedure that would return the next value of the sequence. Procedure would start a transaction, insert a row into the helper table, remember the generated identity value and roll back the transaction. Thus the helper table always remains empty.

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

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

Few notes about the procedure.

关于程序的一些注意事项。

First, it was not obvious how to insert a row into a table that has only one identity column. The answer is DEFAULT VALUES.

首先，如何将一行插入到只有一个标识列的表中并不明显。答案是DEFAULT VALUES。

Then, I wanted procedure to work correctly if it was called inside another transaction. The simple ROLLBACKrolls back everything if there are nested transactions. In my case I need to roll back only INSERTinto the helper table, so I used SAVE TRANSACTION.

然后，如果它在另一个事务中被调用，我希望它能够正常工作。ROLLBACK如果存在嵌套事务，则简单回滚所有内容。在我的情况下，我只需要回滚INSERT到帮助表中，所以我使用了SAVE TRANSACTION.

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.

没有 savepoint_name 或 transaction_name 的 ROLLBACK TRANSACTION 回滚到事务的开头。当嵌套事务时，这个相同的语句将所有内部事务回滚到最外面的 BEGIN TRANSACTION 语句。

This is how I use the procedure (inside some other big procedure that, for example, creates a new contract):

这就是我使用程序的方式（在其他一些大程序中，例如，创建一个新合同）：

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

It all works fine if you need to generate sequence values one at a time. In case of contracts, each contract is created individually, so this approach works perfectly. I can be sure that all contracts always have unique contract numbers.

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

NB: Just to prevent possible questions. These contract numbers are in addition to surrogate identity key that my Contracts table has. The surrogate key is internal key that is used for referential integrity. The generated contract number is a human-friendly number that is printed on the contract. Besides, the same Contracts table contains both final contracts and Proposals, which can become contracts or can remain as proposals forever. Both Proposals and Contracts hold very similar data, that's why they are kept in the same table. Proposal can become a contract by simply changing the flag in one row. Proposals are numbered using a separate sequence of numbers, for which I have a second table SequenceProposalNumberand second procedure GetNewProposalNumber.

注意：只是为了防止可能出现的问题。这些合同编号是我的合同表所具有的代理身份密钥的补充。代理键是用于参照完整性的内部键。生成的合约编号是印在合约上的人性化编号。此外，同一个 Contracts 表包含最终合同和 Proposals，它们可以成为合同，也可以永远保留为提案。Proposals 和 Contracts 都拥有非常相似的数据，这就是为什么它们被保存在同一个表中的原因。只需更改一行中的标志，提案就可以成为合同。提案使用单独的数字序列编号，为此我有第二个表格SequenceProposalNumber和第二个程序GetNewProposalNumber。

Recently, though, I came across a problem. I needed to generate sequence values in a batch, rather than one-by-one.

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

I need a procedure that would process all payments that were received during a given quarter in one go. The result of such processing could be ~20,000 transactions that I want to record in the Transactionstable. I have similar design here. Transactionstable has internal IDENTITYcolumn that end user never sees and it has a human-friendly Transaction Number that would be printed on the statement. So, I need a way to generate a given number of unique values in a batch.

我需要一个程序来一次性处理在给定季度收到的所有付款。这种处理的结果可能是我想在Transactions表中记录的大约 20,000 个事务。我这里有类似的设计。Transactionstable 具有IDENTITY最终用户永远不会看到的内部列，并且它具有将打印在语句上的人性化事务编号。所以，我需要一种方法来批量生成给定数量的唯一值。

Essentially, I used the same approach, but there are few peculiarities.

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

First, there is no direct way to insert multiple rows in a table with only one IDENTITYcolumn. Though there is a workaround by (ab)using MERGE, I didn't use it in the end. I decided that it was easier to add a dummy Fillercolumn. My Sequence table is going to be always empty, so extra column doesn't really matter.

首先，没有直接的方法可以在只有一IDENTITY列的表中插入多行。尽管 (ab)using 有一个解决方法MERGE，但我最终没有使用它。我决定添加一个虚拟Filler列更容易。我的序列表将始终为空，因此额外的列并不重要。

The helper table looks like this:

辅助表如下所示：

CREATE TABLE [dbo].[SequenceS2TransactionNumber]
(
    [S2TransactionNumber] [int] IDENTITY(1,1) NOT NULL,
    [Filler] [int] NULL,
    CONSTRAINT [PK_SequenceS2TransactionNumber] 
    PRIMARY KEY CLUSTERED ([S2TransactionNumber] ASC)
)

The procedure looks like this:

该过程如下所示：

-- 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

And this is how it is used (inside some big stored procedure that calculates transactions):

这就是它的使用方式（在一些计算事务的大型存储过程中）：

-- 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;

There are few things here that require explanation.

这里有几件事需要解释。

I need to insert a given number of rows into the SequenceS2TransactionNumbertable. I use a helper Numberstable for this. This table simply holds integer numbers from 1 to 100,000. It is used in other places in the system as well. I check if there is enough rows in the Numberstable and expand it to 100,000 * 100,000 by cross joining with itself if needed.

我需要在SequenceS2TransactionNumber表中插入给定数量的行。我Numbers为此使用了一个辅助表。该表仅包含从 1 到 100,000 的整数。它也用于系统中的其他地方。我检查表中是否有足够的行，Numbers并在需要时通过与自身交叉连接将其扩展到 100,000 * 100,000。

I have to save the result of the bulk insert somewhere and pass it to the caller somehow. One way to pass a table outside of the stored procedure is to use a temporary table. I can't use table-valued parameter here, because it is read-only unfortunately. Also, I can't directly insert the generated sequence values into the temporary table #NewS2TransactionNumbers. I can't use #NewS2TransactionNumbersin the OUTPUTclause, because ROLLBACKwill clean it up. Fortunately, the table variables are not affected by the ROLLBACK.

我必须将批量插入的结果保存在某处并以某种方式将其传递给调用者。在存储过程之外传递表的一种方法是使用临时表。我不能在这里使用表值参数，因为不幸的是它是只读的。另外，我不能直接将生成的序列值插入到临时表中#NewS2TransactionNumbers。我不能#NewS2TransactionNumbers在OUTPUT子句中使用，因为ROLLBACK会清理它。幸运的是，表变量不受ROLLBACK.

So, I use table variable @TableTransactionNumbersas a destination of the OUTPUTclause. Then I ROLLBACKthe transaction to clean up the Sequence table. Then copy the generated sequence values from table variable @TableTransactionNumbersto the temporary table #NewS2TransactionNumbers, because only temporary table #NewS2TransactionNumberscan be visible to the caller of the stored procedure. The table variable @TableTransactionNumbersis not visible to the caller of the stored procedure.

所以，我使用表变量@TableTransactionNumbers作为OUTPUT子句的目的地。然后我ROLLBACK用事务来清理 Sequence 表。然后将生成的序列值从表变量复制@TableTransactionNumbers到临时表#NewS2TransactionNumbers，因为#NewS2TransactionNumbers存储过程的调用者只能看到临时表。表变量@TableTransactionNumbers对存储过程的调用者不可见。

Also, it is possible to use OUTPUTclause to send the generated sequence directly to the caller (as you can see in the commented variant that uses MERGE). It works fine by itself, but I needed the generated values in some table for further processing in the calling stored procedure. When I tried something like this:

此外，可以使用OUTPUT子句将生成的序列直接发送给调用者（如您在使用的注释变体中所见MERGE）。它本身工作正常，但我需要在某个表中生成值，以便在调用存储过程中进一步处理。当我尝试这样的事情时：

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

I was getting an error

我收到一个错误

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

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

But, I need ROLLBACKinside the EXEC, that's why I ended up having so many temporary tables.

但是，我需要ROLLBACK内部EXEC，这就是为什么我最终拥有这么多临时表的原因。

After all this, how nice would it be to switch to the latest version of SQL server which has a proper SEQUENCEobject.

毕竟，切换到具有适当SEQUENCE对象的最新版本的 SQL 服务器该有多好。

Answer 3

回答by matt b

An Identity columnis roughly analogous to a sequence.

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

Answer 4

回答by Corey Trager

You could just use plain old tables and use them as sequences. That means your inserts would always be:

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

BEGIN TRANSACTION  
SELECT number from plain old table..  
UPDATE plain old table, set the number to be the next number  
INSERT your row  
COMMIT

But don't do this. The locking would be bad...

但不要这样做。锁定会很糟糕...

I started on SQL Server and to me, the Oracle "sequence" scheme looked like a hack. I guess you are coming from the other direction and to you, and scope_identity() looks like a hack.

我从 SQL Server 开始，对我来说，Oracle“序列”方案看起来像一个黑客。我猜你是从另一个方向过来的，scope_identity() 看起来像一个黑客。

Get over it. When in Rome, do as the Romans do.

克服它。在罗马做到入乡随俗。

Answer 5

回答by George Siggouroglou

The way that i used to solve this problem was a table 'Sequences' that stores all my sequences and a 'nextval' stored procedure.

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

Sql Table:

Sql表：

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

The PK_Sequencesis used just to be sure that there will never be sequences with the same name.

使用PK_Sequences只是为了确保永远不会有同名的序列。

Sql Stored Procedure:

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 some sequences:

插入一些序列：

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);

Finally get next value of a sequence,

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

execute nextval 'SEQ_Participant';

Some c# code to get the next value from Sequence table,

一些 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;
}

Answer 6

回答by James Cane

In SQL Server 2012, you can simply use

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

CREATE SEQUENCE

In 2005 and 2008, you can get an arbitrary list of sequential numbers using a common table expression.

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

Here's an example (note that the MAXRECURSION option is important):

这是一个示例（注意 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)

Answer 7

回答by user36804

Sequences as implemented by Oracle require a call to the database before the insert. identities as implemented by SQL Server require a call to the database after the insert.

Oracle 实现的序列需要在插入之前调用数据库。SQL Server 实现的身份要求在插入后调用数据库。

One is no more hackish than the other. The net effect is the same - a reliance/dependency on the data store to provide unique artificial key values and (in most cases) two calls to the store.

一个并不比另一个更黑客。最终效果是相同的 - 依赖/依赖于数据存储提供唯一的人工键值和（在大多数情况下）对存储的两次调用。

I'm assuming that your relational model is based on artificial keys, and in this context, I'll offer the following observation:

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

We should never seek to imbue artificial keys with meaning; their only purpose should be to link related records.

我们永远不应该试图给人造钥匙灌输意义；它们的唯一目的应该是链接相关记录。

What is your need related to ordering data? can it be handled in the view (presentation) or is it a true attribute of your data which must be persisted?

您对订购数据的需求是什么？它可以在视图（演示）中处理还是必须保留的数据的真实属性？

Answer 8

回答by Paul Klotka

Create a stage table with an identifier on it.

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

Before loading the stage table, truncate and reseed the identifier to start at 1.

在加载阶段表之前，截断并重新设定标识符以从 1 开始。

Load your table. Each row now has a unique value from 1 to N.

加载你的桌子。现在每一行都有一个从 1 到 N 的唯一值。

Create a table that holds sequence numbers. This could be several rows, one for each sequence.

创建一个包含序列号的表。这可能是多行，每个序列一行。

Lookup the sequence number from the sequence table you created. Update the seqence number by adding the number of rows in the stage table to the sequence number.

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

Update the stage table identifier by adding the seqence number you looked up to it. This is an easy one step process. or Load your target table, add the sequence number to the identifier as you load in ETL. This can take advantage of the bulk loader and allow for other transformations.

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

Answer 9

回答by Trident D'Gao

Consider the following snippet.

考虑以下片段。

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

Answer 10

回答by Tony L.

As sqljunkiesshare states, sequences were added to SQL Server 2012. Here's how to do it in the GUI. This is the equivolent of:

正如sqljunkiesshare 所述，序列已添加到 SQL Server 2012。以下是在 GUI 中执行此操作的方法。这是等价的：

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

In the Object Explorer, expand the Programmabilityfolder
Under the Programmabilityfolder, right click on the Sequencesfolder as shown below:

在Object Explorer 中，展开Programmability文件夹
在Programmability文件夹下，右键单击Sequences文件夹，如下所示：

Underlined are the values that you would update to get the equivalent of the SQL statement above, however, I would consider changing these depending on your needs (see notes below).

下划线是您将更新以获得与上述 SQL 语句等效的值，但是，我会考虑根据您的需要更改这些值（请参阅下面的注释）。

Notes:

笔记：

The default Start value, Minimum value, and Maximum value were determined by the range of the data type which was an int in this case. See here for more data type ranges if you want to use something other than an int.
Pretty good chance you'll want your sequence to start at 1 and you might want you minimum value as 1, too.

默认的起始值、最小值和最大值由数据类型的范围决定，在本例中为 int。如果您想使用 int 以外的其他内容，请参阅此处了解更多数据类型范围。
很有可能您希望序列从 1 开始，并且您可能希望最小值也为 1。

您将如何在 Microsoft SQL Server 中实现序列？

提问by Nathan Lee

回答by sqljunkieshare

回答by Vladimir Baranov

回答by matt b

回答by Corey Trager

回答by George Siggouroglou

回答by James Cane

回答by user36804

回答by Paul Klotka

回答by Trident D'Gao

回答by Tony L.

相关推荐

最近更新

标签

您将如何在 Microsoft SQL Server 中实现序列？

提问by Nathan Lee

回答by sqljunkieshare

回答by Vladimir Baranov

回答by matt b

回答by Corey Trager

回答by George Siggouroglou

回答by James Cane

回答by user36804

回答by Paul Klotka

回答by Trident D'Gao

回答by Tony L.

相关推荐

如果 SQL Server 2000 中存在表，如何删除该表？

如何？参数和 LIKE 语句 SQL

选择一列 DISTINCT SQL

SQL 计算两个日期之间的工作日

相关推荐

最近更新

标签