SQL 如何知道表的下一个主键值而不在sql server中插入记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8612580/
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
how to know next primary key value of table without inserting record in sql server?
提问by amit patel
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.
即让我们有 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.
请提供解决方案。
回答by Adriaan Stander
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 在评论中提到的，在您请求该值的时间和您使用它的时间之间，另一个事务可能已插入到此表中，从而假设检索到的值无效。
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)
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
回答by Spencer Ruport
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?
那么你想要未来的身份密钥的理由是什么？
回答by marc_s
With the current version of SQL Server (2008 R2), and using the IDENTITYmechanism, you CANNOTknow the next value ahead of time. There is noproper, 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 SEQUENCESwhich is almost the same as IDENTITYcolumns - but stand-alone, and with sequences, you can ask for the next value (and then use it)
SQL Server 2012（“Denali”）将具有SEQUENCES与IDENTITY列几乎相同的内容- 但是是独立的，并且带有序列，您可以要求下一个值（然后使用它）
Read more about sequences:
阅读有关序列的更多信息：
回答by amit patel
select IDENT_CURRENT('users')
选择 IDENT_CURRENT('用户')
select IDENT_CURRENT('table name')
选择 IDENT_CURRENT('表名')
回答by Harishankar
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 更改记录，或者如果用户取消则删除记录插入。无论哪种方式，它都比使用虚拟键好。
回答by Andreas Dinauer
Let's assume that your primary key uses the concept of auto-increment. This means that you can do the following. Get the highest id and add 1 to it. "id" is unique and uses auto-increment. "table" is your database.
让我们假设您的主键使用自动增量的概念。这意味着您可以执行以下操作。获取最高的 id 并将其加 1。“id”是唯一的并使用自动递增。“表”是您的数据库。
SELECT MAX(id) FROM table
This will return the highest id. Just add 1.
这将返回最高的 id。只需加1。
