Geek Answers Handbook

How to find out the following primary key value of a table without inserting a record into sql server?

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

How to find out the following primary key value without inserting a record?

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

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

In short, the following is the primary key value.

How can i get this?

Please provide a solution.

+5
source share
5 answers

EDIT (Very Important)

It should be noted that this method can be used to predict the next id, but not gaurentee this value. The reason for this is because @marc_s is mentioned in the comments, that between the time you requested the value and the time you are using, another transaction could be inserted into this table, making the assumption that the received value is invalid.

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

From IDENT_CURRENT (Transact-SQL)

, , . .

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
+8

, , . , , , , , . : ", ", - , .

, ?

+3

SQL Server (2008 R2) IDENTITY . , , - , , .

SQL Server 2012 ( "Denali" ) SEQUENCES, IDENTITY, , ( )

:

+1

IDENT_CURRENT ( "" )

IDENT_CURRENT (' ')

0

, , , , UPDATE, , DELETE , . , , .

0

More articles:


All Articles