Oracle序列但在MS SQL Server中

Question

在Oracle中有一种生成序列号的机制，例如：

CREATE SEQUENCE supplier_seq

    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

然后执行该语句

supplier_seq.nextval

如何在MS SQL Server中创建相同的功能以检索下一个序列号？

编辑：我不是在寻找为表记录自动生成键的方法。我需要生成一个唯一值，我可以将其用作过程的（逻辑）ID。因此，我需要Oracle提供的确切功能。

- Raymond

Answer 1

没有完全匹配的结果。

相应的解决方案是在创建表时设置为数据类型的IDENTITY。在插入时，SQL Server会自动创建一个运行序列号。 可以通过调用SCOPE_IDENTITY()或查询系统变量@@IDENTITY（如Frans所指出的那样）来获取最后插入的值。

如果您需要精确的等价物，则需要创建一个表，然后编写一个过程来返回下一个值和其他操作。请参见Marks关于这方面潜在问题的回答。

编辑：
SQL Server已经实现了类似于Oracle的Sequence。请参考此问题获取更多详细信息。

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

- Dheer

---

我需要确切的等价物，使用过程也是第一个想法。我在想是否有更“自然”的解决方案。 - Raymond

那你就没那么幸运了。 - Dheer

根据https://dev59.com/43VC5IYBdhLWcg3weA8-和http://msdn.microsoft.com/en-us/library/ff878091.aspx，SQL Server 2012似乎引入了SEQUENCE对象。 - Loudenvier

Answer 2

身份验证是最好和最可扩展的解决方案，但是，如果你需要的序列不是递增的整数，比如00A、00B、00C或者一些特殊序列，那么有第二好的方法。如果正确地实现，它可以良好扩展，但是如果实现得不好，它会很难扩展。我不太建议使用它，但是你可以这样做：

1. 你需要将“下一个值”存储在表中。这个表可以是一个简单的、一行一列的表，只包含该值。如果你有多个序列，它们可以共享这个表，但是为每个序列使用单独的表可能会减少争用。
2. 你需要编写一个单独的更新语句，以1个间隔递增该值。你可以将这个更新语句放在一个存储过程中，使其易于使用，并防止在不同位置的代码中重复使用。
3. 正确使用序列（虽然不如Identity那样好）需要两个步骤：a. 更新语句具有专门为此问题设计的特殊语法，可以在一条语句中同时递增和返回值；b. 你必须在事务开始之前和事务范围之外从自定义序列中获取值。这就是Identity比较好的一个原因——它返回一个新值，不考虑事务范围，对于任何尝试插入的操作，但在失败时不会回滚。这意味着它不会阻塞，也意味着你将会有一些失败的交易造成的间隙。

特殊的更新语法在不同版本中略有不同，但要点是在同一语句中对变量进行赋值和更新。对于2008年，Itzik Ben-Gan提供了这个巧妙的解决方案：http://www.sqlmag.com/Articles/ArticleID/101339/101339.html?Ad=1 旧式的2000及以后的方法如下：

UPDATE SequenceTable SET @localVar = value = value + 5 -- 将尾部更改为您的增量逻辑

这将同时增加并返回下一个值。

如果您绝对不能有间隙（抵制该要求:-)），那么在事务的其余部分中放置该更新或过程在技术上是可能的，但每次插入都需要等待前一个提交，这会带来很大的并发性问题。

我不能因此获得荣誉；我从Itzik那里学到了这一切。

- onupdatecascade

Answer 3

将字段设为标识字段，该字段的值将自动获取。您可以通过调用SCOPE_IDENTITY()或查询系统变量@@IDENTITY来获得上次插入的值。

首选使用SCOPE_IDENTITY()函数。

- Frans Bouma

Answer 4

正如DHeer所说，绝对没有完全匹配的解决方案。如果您试图构建自己的过程来执行此操作，则不可避免地会使您的应用程序停止扩展。Oracle的序列是高度可扩展的。好吧，我稍微收回一下。如果您真的愿意专注于并发，并且愿意接受可能存在顺序上的偏差，那么您有机会。但是，由于您似乎对t-sql并不太熟悉，我建议在（将Oracle应用程序移植到MSSS - 这是您正在做的吗）时开始寻找其他选项。例如，只需在“nextval”函数中生成GUID。这样可以实现扩展。哦，不要使用一个表存储所有值，仅保留您在缓存中的最大值。您必须锁定它以确保提供唯一值，这就是您停止扩展的地方。您必须找出是否有一种在内存中缓存值并对某种轻量级锁进行编程访问的方法 - 内存锁，而不是表锁。

- Mark Brady

---

Oracle序列中可能存在间隔，但是否会出现乱序？ - tuinstoel

它就在DDL序列中。指定ORDER以确保按请求顺序生成序列号。如果您将序列号用作时间戳，则此子句很有用。保证顺序通常对于用于生成主键的序列不重要。 - Mark Brady

Answer 5

这个问题可能早就有答案了...但是从SQL 2005开始，你可以使用ROW_NUMBER函数...一个例子如下：

select ROW_NUMBER() OVER (ORDER BY productID) as DynamicRowNumber, xxxxxx,xxxxx

OVER 语句在我的情况下使用 ORDER BY 来作为唯一主键...

希望这可以帮到你... 不再需要临时表或奇怪的连接！！

- Dav.id

Answer 6

我希望SQL Server有这个功能，这将使很多事情变得更容易。

以下是我解决这个问题的方法。

创建一个名为tblIdentities的表。在此表中放置一行，包含您的最小值和最大值以及序列号应该重置的频率。还要放置一个新表的名称（称其为tblMySeqNum）。这样做可以使以后添加更多序列号生成器变得相对容易。

tblMySeqNum有两列。ID（它是一个int identity）和InsertDate（它是一个带有默认值GetDate()的日期时间列）。

当您需要一个新的序列号时，请调用一个存储过程将其插入到此表中，并使用SCOPE_IDENTITY()获取创建的标识。确保您没有超过tblIdentities中的最大值。如果超过了，则返回错误。如果没有超过，则返回您的序列号。

现在，进行重置和清理。每隔一段时间运行一个作业，检查 tblIdentites 中列出的所有表（暂时只有一个表）是否需要重置。如果它们达到了重置值或时间，则调用 DBCC IDENT RESEED 并传入行中列出的表名（例如，在此示例中为 tblMySeqNum）。同时，这也是清除表中不必要行的好时机。

不要在获取标识的存储过程中进行清理或重新设置。如果这样做，您的序列号生成器将无法很好地扩展。

正如我所说，如果此功能在 SQL Server 中，那么很多事情都会更容易，但我发现这个解决方法相当好用。

Vaccano

- Vaccano

---

喜欢得到负评，但有些投票者太胆怯了，不敢说下降的原因... - Vaccano

Answer 7

如果您能升级到SQL Server 2012，您可以使用SEQUENCE对象。即使是SQL Server 2012 Express也支持序列。

CREATE SEQUENCE supplier_seq
    AS DECIMAL(38)
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

SELECT NEXT VALUE FOR supplier_seq
SELECT NEXT VALUE FOR supplier_seq
SELECT NEXT VALUE FOR supplier_seq
SELECT NEXT VALUE FOR supplier_seq
SELECT NEXT VALUE FOR supplier_seq

结果为：

---------------------------------------
1

(1 row(s) affected)


---------------------------------------
2

(1 row(s) affected)


---------------------------------------
3

(1 row(s) affected)


---------------------------------------
4

(1 row(s) affected)


---------------------------------------
5

(1 row(s) affected)

请确保指定正确的数据类型。如果我没有指定它，您提供的最大值将不被接受，这就是为什么我使用了具有尽可能高精度的DECIMAL。

关于SEQUENCES的更多信息，请参见：http://msdn.microsoft.com/en-us/library/ff878091.aspx

- Loudenvier

Answer 8

正是因为这个原因，IDENT_CURRENT 不受范围和会话的限制；它仅限于指定的表。因此我们需要使用 SCOPE_IDENTITY()，因为作用域标识将为我们提供在我们的会话中生成的唯一数字，并且唯一性由标识本身提供。

- ivan.ukr

Answer 9

这不是一个确切的答案，而是对一些现有答案的补充

SCOPE_IDENTITY (Transact-SQL)

SCOPE_IDENTITY、IDENT_CURRENT和@@IDENTITY是类似的函数，因为它们返回插入到标识列中的值。

IDENT_CURRENT没有范围和会话的限制；它仅限于指定的表。IDENT_CURRENT返回在任何会话和任何范围内为特定表生成的值。有关更多信息，请参见IDENT_CURRENT (Transact-SQL)。

这意味着两个不同的会话可以具有相同的标识值或序列号，因此为了避免这种情况并获得所有会话的唯一编号，请使用IDENT_CURRENT

- bjan

Answer 10

这并不是一个真正的答案，但看起来在2012年SQLServer中会引入序列。

http://www.sql-server-performance.com/2011/sequence-sql-server-2011/

- Keith