SQL 在不插入行的情况下获取下一个 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1542886/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting the next ID without inserting a row
提问by iWeasel
Is it possible in SQL (SQL Server) to retrieve the next ID (integer) from an identity column in a table before, and without actually, inserting a row? This is not necessarily the highest ID plus 1 if the most recent row was deleted.
是否可以在 SQL (SQL Server) 中从表中的标识列中检索下一个 ID（整数），而不实际插入行？如果最近的行被删除，这不一定是最高 ID 加 1。
I ask this because we occassionally have to update a live DB with new rows. The ID of the row is used in our code (e.g. Switch (ID){ Case ID: } and must be the same. If our development DB and live DB get out of sync, it would be nice to predict a row ID in advance before deployment.
我问这个是因为我们有时必须用新行更新实时数据库。我们的代码中使用了行的ID（例如Switch(ID){Case ID:}并且必须相同。如果我们的开发DB和Live DB不同步，提前预测一个行ID会很好部署前。
I could of course SET IDENTITY OFFSET INSERT_IDENTITY ON or run a transaction (does this roll back the ID?) etc but wondered if there was a function that returned the next ID (without incrementing it).
我当然可以SET IDENTITY OFFSET INSERT_IDENTITY ON 或运行事务（这会回滚 ID 吗？）等，但想知道是否有返回下一个 ID 的函数（不增加它）。
采纳答案by Andrew
Edit:
编辑：
After spending a number of hours comparing entire page dumps, I realised there is an easier way and I should of stayed on the DMVs.
在花了几个小时比较整个页面转储后，我意识到有一个更简单的方法，我应该留在 DMV 上。
The value survives a backup / restore, which is a clear indication that it is stored - I dumped all the pages in the DB and couldn't find the location / alteration for when a record was added. Comparing 200k line dumps of pages isn't fun.
该值在备份/恢复后仍然存在，这清楚地表明它已存储 - 我转储了数据库中的所有页面，但找不到添加记录时的位置/更改。比较 200k 行的页面转储并不有趣。
I had used the dedicated admin console I took a dump of every single internal table exposed inserted a row and then took a further dump of the system tables. Both of the dumps were identical, which indicates that whilst it survived, and therefore must be stored, it is not exposed even at that level.
我使用了专用的管理控制台，我转储了插入一行的每个内部表，然后进一步转储了系统表。两个转储是相同的，这表明虽然它幸存下来，因此必须存储，但即使在那个级别也不会暴露。
So after going around in a circle I realised the DMV did have the answer.
所以转了一圈后我意识到DMV确实有答案。
create table foo (MyID int identity not null, MyField char(10))
insert into foo values ('test')
go 10
-- Inserted 10 rows
select Convert(varchar(8),increment_value) as IncrementValue,
Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'
-- insert another row
insert into foo values ('test')
-- check the values again
select Convert(varchar(8),increment_value) as IncrementValue,
Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'
-- delete the rows
delete from foo
-- check the DMV again
select Convert(varchar(8),increment_value) as IncrementValue,
Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'
-- value is currently 11 and increment is 1, so the next insert gets 12
insert into foo values ('test')
select * from foo
Result:
MyID MyField
----------- ----------
12 test
(1 row(s) affected)
Just because the rows got removed, the last value was not reset, so the last value + increment should be the right answer.
仅仅因为行被删除，最后一个值没有被重置，所以最后一个值 + 增量应该是正确的答案。
Also going to write up the episode on my blog.
也打算在我的博客上写这一集。
Oh, and the short cut to it all:
哦，还有捷径：
select ident_current('foo') + ident_incr('foo')
So it actually turns out to be easy - but this all assumes no one else has used your ID whilst you got it back. Fine for investigation, but I wouldn't want to use it in code.
所以它实际上很容易 - 但这一切都假设在您取回 ID 时没有其他人使用过您的 ID。适合调查，但我不想在代码中使用它。
回答by Himadri
try IDENT_CURRENT:
Select IDENT_CURRENT('yourtablename')
This works even if you haven't inserted any rows in the current session:
即使您没有在当前会话中插入任何行，这也有效：
Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.
返回为指定表或视图生成的最后一个标识值。生成的最后一个标识值可以用于任何会话和任何范围。
回答by Jeff Hornby
This is a little bit strange but it will work:
这有点奇怪，但它会起作用：
If you want to know the next value, start by getting the greatest value plus one:
如果您想知道下一个值，请先获取最大值加一：
SELECT max(id) FROM yourtable
To make this work, you'll need to reset the identity on insert:
要使其工作，您需要在插入时重置标识：
DECLARE @value INTEGER
SELECT @value = max(id) + 1 FROM yourtable
DBCC CHECKIDENT (yourtable, reseed, @value)
INSERT INTO yourtable ...
Not exactly an elegant solution but I haven't had my coffee yet ;-)
不完全是一个优雅的解决方案，但我还没有喝咖啡 ;-)
(This also assumes that there is nothing done to the table by your process or any other process between the first and second blocks of code).
（这也假设您的进程或第一和第二个代码块之间的任何其他进程没有对表做任何事情）。
回答by Mitch Wheat
Rather than using an IDENTITY column, you could use a UNIQUEIDENTIFIER (Guid) column as the unique row identifer and insert known values.
您可以使用 UNIQUEIDENTIFIER (Guid) 列作为唯一行标识符并插入已知值，而不是使用 IDENTITY 列。
The other option (which I use) is SET IDENTITY_INSERT ON, where the row IDs are managed in a source controlled single 'document'.
另一个选项（我使用）是 SET IDENTITY_INSERT ON，其中行 ID 在源控制的单个“文档”中进行管理。
回答by marc_s
You can pretty easily determine that the last value usedis:
您可以很容易地确定最后使用的值是：
SELECT
last_value
FROM
sys.identity_columns
WHERE
object_id = OBJECT_ID('yourtablename')
Usually, the next ID will be last_value + 1 - but there's no guarantee for that.
通常，下一个 ID 将是 last_value + 1 - 但不能保证。
Marc
马克
