如何在不在sql server中插入记录的情况下知道表的下一个主键值？

I have a things in my project where i need to display next primary key in field without inserting the record physically?

我的项目中有一些东西，我需要在字段中显示下一个主键而不在物理上插入记录？

How can i know the next value of primary key without inserting record?

如何在不插入记录的情况下知道主键的下一个值？

i.e. Lets have 10 records with ID columns as primary key. Now I have deleted 10th record, so the next value will be 11.

即，Lets有10条记录，ID列为主键。现在我删除了第10条记录，所以下一个值将是11。

I want to know the value of next primary key (11 in my case) without inserting the record physically in the table.

我想知道下一个主键的值（在我的情况下是11），而不是在表中物理地插入记录。

In short, the future next value of the primary key.

简而言之，未来下一个主键的价值。

How can i get that??

我该怎么办？

Please provide the solution.

请提供解决方案。

5 个解决方案

#1

EDIT (Very important)

编辑（非常重要）

It should be noted that this method can be used to predict the next id, but does not gaurentee this value. The reason for this is as @marc_s mentioned in the comments, that between the time that you requested the value, and the time you use it, another transaction could have inserted into this table, making the assumption of the value retrieved null and void.

应该注意的是，此方法可用于预测下一个id，但不会保证此值。这样做的原因是注释中提到的@marc_s，在您请求值的时间和使用它的时间之间，另一个事务可能已插入此表，使得检索的值的假设为null和void。

As mentioned, if your implementation is based on such an assumption, you have made some design errors, and should look at reworking this solution as a first priority.

如上所述，如果您的实现基于这样的假设，那么您已经犯了一些设计错误，并且应该将此解决方案作为第一优先级进行重新设计。

From IDENT_CURRENT (Transact-SQL)

来自IDENT_CURRENT（Transact-SQL）

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.

返回为指定表或视图生成的最后一个标识值。生成的最后一个标识值可以用于任何会话和任何范围。

Have a look at the following example

看看下面的例子

CREATE TABLE #Table (
        ID INT IDENTITY(1,1),
        Val INT
)

INSERT INTO #Table SELECT 1
INSERT INTO #Table SELECT 2
INSERT INTO #Table SELECT 3

SELECT * FROM #Table

DELETE FROM #Table WHERE ID >= 2

SELECT * FROM #Table

SELECT IDENT_CURRENT('#Table')

DROP TABLE #Table

#2

What you're asking doesn't really make sense. Databases are designed to support multiple access so even if there was a way to determine the next primary key identity without inserting a record there would be zero guarantee that another connection wouldn't write to that table and claim that identity before your next transaction completes. And if you're thinking "Well I could keep a lock on it" then you've basically eliminated any sort of plausible situation where knowing the future identity key might help you.

你问的问题真的没有意义。数据库旨在支持多个访问，因此即使有一种方法可以在不插入记录的情况下确定下一个主键标识，也不能保证另一个连接不会写入该表并在下一个事务完成之前声明该标识。如果你正在考虑“我能保持锁定”，那么你基本上已经消除了任何可能的情况，因为知道未来的身份密钥可能对你有帮助。

So what is your reasoning for wanting the future identity key?

那么你想要未来身份密钥的理由是什么？

#3

With the current version of SQL Server (2008 R2), and using the IDENTITY mechanism, you CANNOT know the next value ahead of time. There is no proper, guaranteed way to know the next value until you've actually inserted the row - only then, when the row is stored inside the table, that value is determined and returned.

使用当前版本的SQL Server（2008 R2）并使用IDENTITY机制，您无法提前知道下一个值。在您实际插入行之前，没有正确的，有保证的方法来知道下一个值 - 只有当行存储在表中时，才会确定并返回该值。

SQL Server 2012 ("Denali") will have SEQUENCES which is almost the same as IDENTITY columns - but stand-alone, and with sequences, you can ask for the next value (and then use it)

SQL Server 2012（“Denali”）将具有与IDENTITY列几乎相同的SEQUENCES - 但是对于序列，您可以请求下一个值（然后使用它）

#4

select IDENT_CURRENT('users')

选择IDENT_CURRENT（'用户'）

select IDENT_CURRENT('table name')

选择IDENT_CURRENT（'表名'）

#5

Not really a great idea, but if you want a guaranteed primary key my suggestion would be to insert a dummy record temporarily and get its primary key and then either change the record using UPDATE when the record is committed or DELETE the record if the user cancels insertion. Either way, it's better than using an imaginary key.

这不是一个好主意，但是如果你想要一个保证的主键，我的建议是暂时插入一个虚拟记录并获取其主键，然后在提交记录时使用UPDATE更改记录，或者如果用户取消则删除记录插入。无论哪种方式，它都比使用虚拟键更好。

#1