如何从SQL Server获取下一个标识值

Question

我需要从SQL Server中获取下一个身份值。

我使用这段代码：

SELECT IDENT_CURRENT('table_name') + 1

这是正确的，但当 table_name 为空（且下一个标识值为“1”）时返回了“2”，但结果应该是“1”

- Mehdi Esmaeili

---

在向表中插入新行之前，您不能可靠地找到下一个身份值。不要再试了，您无法成功。接受这个事实：在实际将行插入表并由SQL Server分配值之前，您无法知道identity值。 - marc_s

我的问题是为什么。听起来你正在试图以更冗长的方式完成已经很容易实现的事情。请解释一下你想要做什么，也许有更好的方法。你不应该试图得到未来可能不存在的东西，因为当你使用它的时候，它可能已经改变了。如果你继续，请告诉我是哪个应用程序，这样我就可以确保将来不会使用它。 :) - user1853517

Answer 1

我认为你需要寻找一种替代方法来计算下一个可用值（例如将列设置为自动增量）。

从IDENT_CURRENT文档中可以看到，对于空表：

当IDENT_CURRENT值为NULL（因为该表从未包含过行或已被截断），IDENT_CURRENT函数将返回种子值。

这甚至似乎并不是很可靠，特别是如果你最终设计的应用程序有多个人同时写入该表。

谨慎使用IDENT_CURRENT来预测下一个生成的标识值。由于其他会话执行的插入操作，实际生成的值可能与IDENT_CURRENT加上IDENT_INCR不同。

- Grant Winney

---

你无法可靠地计算下一个可用值 - 这根本是不可能的。涉及太多情况和条件，你永远也无法正确预测。不要这样做 - 不要试图提前计算。这不是SQL Server中IDENTITY工作的方式。 - marc_s

谢谢... 有没有办法找出它是否为空？ - Mehdi Esmaeili

Answer 2

如果您的表为空，则此查询将完美运行。

SELECT
  CASE
    WHEN (SELECT
        COUNT(1)
      FROM tablename) = 0 THEN 1
    ELSE IDENT_CURRENT('tablename') + 1
  END AS Current_Identity;

- Abhijit Gosavi

---

案例的第一部分不可靠。可能已经删除了行，但标识符不会重置。 - Grid Trekkor

Answer 3

我知道已经有一个答案，但让我感到不爽的是，所有搜索"获取下一个SQL Server身份认证"的结果都是靠不住的解决方案（比如仅选择当前身份认证值并加1）或者说 "无法可靠地完成"。

实际上有几种方法可以做到这一点。

SQL Server >= 2012

CREATE SEQUENCE dbo.seq_FooId START WITH 1 INCREMENT BY 1
GO

CREATE TABLE dbo.Foos (
    FooId int NOT NULL 
        DEFAULT (NEXT VALUE FOR dbo.seq_FooId)
        PRIMARY KEY CLUSTERED 
)
GO

// Get the next identity before an insert
DECLARE @next_id = NEXT VALUE FOR dbo.seq_FooId

在SQL Server 2012中，出现了SEQUENCE对象。每次调用“NEXT VALUE FOR”时，序列都会递增，因此您不必担心并发问题。

SQL Server <= 2008

CREATE TABLE dbo.Foos (
    FooId int NOT NULL 
        IDENTITY (1, 1)
        PRIMARY KEY CLUSTERED 
)
GO

// Get the next identity before an insert
BEGIN TRANSACTION
SELECT TOP 1 1 FROM dbo.Foos WITH (TABLOCKX, HOLDLOCK)
DECLARE @next_id int = IDENT_CURRENT('dbo.Foos') + IDENT_INCR('dbo.Foos');
DBCC CHECKIDENT('dbo.Foos', RESEED, @next_id)
COMMIT TRANSACTION

你很可能想要将所有这些内容封装在存储过程中，特别是因为DBCC语句需要提升访问权限，你可能不希望每个人都拥有这种访问权限。

这并不像NEXT VALUE FOR那么优雅，但应该是可靠的。请注意，如果表中没有行，则第一个值为2，但如果您打算始终使用此方法来获取下一个身份标识，则可以在IDENTITY(0,1)中将标识初始化为0而不是1，如果您一定要从1开始，请忽略此建议。

为什么会有人想这样做？

我无法代表问题的发布者发言，但书籍“领域驱动设计”和官方DDD示例使用此技术（或至少暗示了这种技术）来强制实体始终具有有效的标识符。如果您的实体具有虚假的标识符（例如-1或default(int)或null），直到它被INSERT到数据库中，它可能会泄漏持久化问题。

- tuespetre

---

重新设置种子作为标准代码？除了引起颤抖外，如果标识中存在间隙，这种方法将无法可靠地工作。 - Paul

Answer 4

SELECT isnull(IDENT_CURRENT('emp') + IDENT_INCR('emp'),1)

- Islam Gx

Answer 5

我倾向于同意其他发帖者的看法，这不是正确的方法，但对于某些情况来说确实很方便。有几篇帖子询问为什么要这样做，让我举个例子，说明它在我这里是如何方便的以及如何实现和原因。

我正在实现一个比特币节点，并希望将区块链存储在 SQL 数据库中。每个块都从网络中的其他节点和矿工那里接收到。细节可以在其他地方找到。

当接收到一个块时，它包含一个头部、任意数量的交易以及每个交易中任意数量的输入和输出。我的数据库中有 4 个表——你猜对了——一个头部表、交易表、输入表和输出表。事务、输入和输出表中的每一行都与上面的头部行 ID 相关联。

一些块包含数千个交易。某些交易可能有数百个输入和/或输出。我需要它们通过 C# 中方便的调用存储在数据库中，而不会牺牲完整性（所有 ID 都连接起来），并具有良好的性能——当提交近 10000 行时，我无法逐行提交。

相反，我确保在操作期间在 C# 中同步锁定我的数据库对象（我也不必担心其他进程访问数据库），以便我可以方便地在所有 4 个表上执行 IDENT_CURRENT，从存储过程返回值，用增加的 ID 填充近 10000 行中的 4 个 List<DBTableRow>，同时调用 SqlBulkCopy.WriteToServer 方法，并设置选项 SqlBulkCopyOptions.KeepIdentity，然后通过 4 个简单的调用将它们全部发送，一个用于每个表集。

性能的提高（在一台 4-5 年旧的中档笔记本电脑上）是从大约 60-90 秒到对于非常大的块只需 2-3 秒，因此我很高兴学习了 IDENT_CURRENT()。

这个解决方案可能不太优雅，可能不是按照规范实现的，但它很方便和简单。当然还有其他方法可以完成此任务，但这是直截了当的，并且只需要几个小时就能实现。只要确保没有并发问题即可。

- Søren L. Fog

---

忘记了一个细节，如果有任何读者想要做我所做的事情。请将执行所有SqlBulkCopy.WriteToServer调用的方法包装在事务内，并在保存所有集合时调用Commit，或在出现错误时调用Rollback（例如，在try-catch中）。祝编码愉快 :) - Søren L. Fog

需要知道下一个ID的另一个原因是为了测试性。我有一些有条件插入行的代码，如果其他某些操作失败，则删除该行（由于事务无法帮助）。测试人员无法看到代码，但可以看到数据库。行数的计数（count()）和最大值（max()）不能告诉他们是否发生了插入和删除，而不是什么都没有发生。但是，看到“下一个”ID将告诉他们插入是否发生（在某种程度上是真实的）。 - Ken Forslund

Answer 6

我将假设您有充分的理由而不是问“为什么你要这样做？”以下是我为一些快速测试SQL所做的事情，这在除了一个情况以外的所有情况下都很好用，即使在清空先前填充的表后也是如此。

    DECLARE @nextId INTEGER = IDENT_CURRENT('myTable')
    IF (SELECT COUNT(*) FROM myTable) > 0
    OR @nextId > 1
        SET @nextId += 1

我随后使用此方法构建了一个状态字符串，将其放入myTable中（我希望它具有新行将被赋予的NEW身份值），以验证SQL是否按预期工作。

唯一不正确处理的情况是：

1. 创建一个新的（空）表并运行上述代码。它将正确地告诉您它将具有ID 1。 2. 向表中添加单个元素。这将具有ID 1，如预测所示。 3. 清空表格。 4. 再次运行上述SQL。它将错误地告诉您下一个条目也将具有ID 1。

这是我找不到解决方法的唯一情况。 我可以接受它，因为我的测试步骤1涉及向表中添加多行，但请注意这种不太可能但可能导致错误的事件序列。

可能会有“更好”的方法，但这不是生产代码，所以快速而粗略就足够了。

- Database Dave

Answer 7

我曾经这样使用它来计算下一个ID，当表为空时也能很好地工作。

select isnull(max(ID),0)+1 from yourTable

- Asim Khan

---

欢迎来到 SO。这并不考虑非默认种子或增量，或已删除的记录。 - Zef

Answer 8

我可以提供另一种情况，知道下一个值会很有用。 我在Excel中使用线路中的数据，例如：

A B C X1 X2 Y1 Y2 Z1 Z2

我想将A B C放入一个数据库表中，并在另一个表中使用该记录的新主键来放置X1和X2，然后是Y1和Y2，然后是Z1和Z2。 因此，这一行将在第一个表中生成一条记录：

xxx A B C

并在第二个表中生成三个记录

yyy xxx X1 X2

yyy+1 xxx Y1 Y2

yyy+2 xxx Z1 Z3

如果我能在开始过程之前确定xxx，我可以为正在处理的每条记录递增它。 我也犯了一个错误，就是找到最高值M并假设M + 1将是下一个值。 这不起作用是因为一些上传由于错误而未完成，事务被回滚，但已分配索引的PK不会被重用。 删除最高值PK记录需要手动删除，否则会导致类似问题。

我的简单解决方案是编写一个 SP，在上传第一个案例后获取 PK 的最大值。

CREATE     PROCEDURE [MIRR].[GetLast_CasesIndexID]
@GroupNumber as int Output
AS
BEGIN
    SELECT @GroupNumber = max(CasesIndexID)  
    FROM MIRR.Cases
END
GO

- Freond

Answer 9

实际上，回滚插入操作时，不会回滚给值分配的identity()值，这会导致下一个identity值“跳跃”... 因此将主键视为连续值是不正确的——你假设在你的插入执行之前没有回滚！这是一个递增值，以确保唯一值。不能保证没有间隙...即使你开始插入。

我建议当你遇到需要下一个IDENTITY值的情况时，你真正需要的是与行上不同表的外键-以便你的集合有一种被标识的方法。

另外，你可能需要研究OUTPUT INTO来捕获插入哪些记录以链接到你插入的信息...

INSERT INTO #A(Col1)
OUTPUT inserted.IdentityColumn, inserted.Col1 
INTO #B (DestinationPrimaryKey, SourceRowId)
SELECT SourceRowID FROM #ThingToRecord

SELECT DestinationPrimaryKey, SourceRowId
FROM #B

- Alocyte