how to know next primary key value of table without inserting record in sql server?

Question

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.

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

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

How can i get that??

Please provide the solution.

Answer 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.

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

Answer 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?