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

I need to get the next identity value from SQL Server.

我需要从SQL Server获取下一个标识值。

I use this code :

我用这个代码：

SELECT IDENT_CURRENT('table_name') + 1

This is correct, but when the table_name is empty (and next identity value is "1") returned "2" but result is "1"

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

5 个解决方案

#1

I think you'll want to look for an alternative way to calculate the next available value (such as setting the column to auto-increment).

我想你会想要另一种方法来计算下一个可用值（例如将列设置为自动递增）。

From the IDENT_CURRENT documentation, regarding empty tables:

从IDENT_CURRENT文档中，关于空表：

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

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

It doesn't even seem all that reliable, especially if you end up designing an app that has more than one person writing to the table at the same time.

它甚至看起来都不可靠，特别是如果你最终设计的应用程序有多个人同时写入桌面。

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

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

#2

I tend to agree with other posters that this is not the right way to do this, however it can be convenient for certain cases. Several posts asks why at all do this, and let me give you an example where it was convenient for me, and how and why.

我倾向于同意其他海报，认为这不是正确的方法，但对某些情况来说这可能很方便。几个帖子问为什么要这样做，让我举个例子，它对我来说很方便，以及如何以及为什么。

I'm implementing a Bitcoin node. I want the blockchain stored in an SQL database. Every block is received from the network from other nodes and miners. The details you can find elsewhere.

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

When receiving a block it contains one header, any number of transactions and each transaction any number of inputs and outputs. I have 4 tables in my database - you guessed it - a header table, transaction table, inputs table and outputs table. Each row in the transaction, inputs and outputs table are linked with IDs to eachother up into the header row.

当接收块时，它包含一个头，任意数量的事务和每个事务的任意数量的输入和输出。我的数据库中有4个表 - 你猜对了 - 表头，事务表，输入表和输出表。事务中的每一行，输入和输出表都与ID相互链接到标题行。

Some blocks contain several thousand transactions. Some transactions hundres of inputs and/or outputs. I need them stored in the DB from a convenient call in C# without compromising integrity (the IDs all link up) and with decent performance - which I cannot get by committing row by row when there are close to 10000 commits.

一些块包含数千个事务。一些交易包括投入和/或产出。我需要将它们存储在数据库中，方便调用C＃而不影响完整性（所有ID都链接起来）并具有良好的性能 - 当接近10000次提交时，我无法通过逐行提交来获取它们。

Instead, I make absolutely sure to sync-lock my database object in C# during the operation (and I don't have to worry about other processes accessing the database also), so I can conveniently do a IDENT_CURRENT on all 4 tables, return the values from a stored proc, fill up the nearly 10000 rows in 4 List<DBTableRow> while incrementing the IDs and call the SqlBulkCopy.WriteToServer method with option SqlBulkCopyOptions.KeepIdentity set, and then send it all in 4 simple calls, one for each tableset.

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

The performance gain (on a 4-5 years old mid-range laptop) was going from about 60-90 seconds down to 2-3 seconds for the really large blocks, so I was happy to learn about IDENT_CURRENT().

性能提升（在4-5岁的中档笔记本电脑上）从大约60-90秒下降到真正大块的2-3秒，所以我很高兴了解IDENT_CURRENT（）。

The solution might not be elegant, might not be by the book so to speak, but it is convenient and simple. There are also other ways to accomplish this, I know, but this was just straight forward and took a few hours to implement. Just make sure you don't have concurrency issues.

解决方案可能并不优雅，可能不会出书，但它既方便又简单。我知道，还有其他方法可以实现这一目标，但这只是直截了当，花了几个小时来实现。只是确保您没有并发问题。

#3

In case when your table will be empty then this query will work perfectly.

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

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

#4

I know there is already an answer but it really irks me that all of my searches along the lines of "get next identity sql server" came up with flaky solutions (like merely selecting the current identity value and adding 1) or "it can't be reliably done".

我知道已经有了答案，但我真的很烦我所有我的搜索都是“获取下一个身份sql服务器”提出了片状解决方案（比如只选择当前的身份值并添加1）或“它可以” “可靠地完成”。

There are a couple of ways to actually do this.

有几种方法可以实现这一点。

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 introduced the SEQUENCE object. In this case, the sequence will be incremented each time NEXT VALUE FOR is called, so you don't need to worry about concurrency.

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

You will probably want to encapsulate all of that in a stored procedure, especially because the DBCC statement requires elevated access and you probably won't want everyone having that kind of access.

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

Not anywhere near as elegant as NEXT VALUE FOR, but it should be reliable. Note that you will get 2 for your first value if there are no rows in the table, but if you intend to always use this method to get the next identity, you could seed the identity at 0 instead of 1 (with IDENTITY (0, 1)) if you are dead set on starting off with 1.

并不像NEXT VALUE FOR那样优雅，但它应该是可靠的。请注意，如果表中没有行，则第一个值将获得2，​​但如果您打算始终使用此方法获取下一个标识，则可以将标识置于0而不是1（使用IDENTITY（0， 1））如果你在1开始时设置为死。

Why would anyone want to do this?

I cannot speak for the question poster, but the book 'Domain Driven Design' and the 'official' DDD sample uses this technique (or at least hints at it) as a way of enforcing that entities always have a valid identifier. If your entity has a bogus identifier (like -1 or default(int) or null) until it is INSERTed into the database, it is potentially leaking a persistence concern.

我不能代表问题海报发言，但是“领域驱动设计”和“官方”DDD样本这本书使用了这种技术（或至少暗示了它），作为强制实体始终拥有有效标识符的一种方式。如果您的实体具有伪标识符（例如-1或default（int）或null），直到它被插入到数据库中，它可能会泄漏持久性问题。

#5

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