Get latest identity, such as ID, from an empty table SQL

Question

I have noticed that SQL keeps track of the latest identity (field it automatically increments each time a new record is created). I want to retrieve the latest identity from the table using C#. Unfortunately most of the time the table is empty (records are written and removed after a while, so often the table is empty). Is this possible to do this within the bounds of the C# SQL API or do I have to create a stored procedure to retrieve this?

enter image description here

To better explain. If the row above was removed, the next ID number for the next record would be 32. I want to retrieve 32 before the record is written in, in the situation where the table is empty.

What happens if there are two calls for the next ID, then the next ID assigned might not be what you expect when you go to write your record. — Zachary, Apr 19 '12 at 14:41
Lots of good info in this post: http://stackoverflow.com/questions/567171/scope-identity-vs-ident-current — mgnoonan, Apr 19 '12 at 14:43

Curtis · Accepted Answer · 2012-04-19T14:45:59.503

4

SELECT IDENT_CURRENT('table_name')+1;

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

http://msdn.microsoft.com/en-us/library/ms175098.aspx

However, although this shows what the next ID will be, this doesn't always mean it will be the next ID entered by yourself. Someone else could INSERT a new record before you.

In short, there is no way of returning the value of what you will next be inserting.

edited Apr 19 '12 at 14:45

answered Apr 19 '12 at 14:40

Curtis

101,612
66
270
352

+1 because it answers the question, but you may want to note that the number it returns may not be the ID of the next row that *you* would insert (because someone else inserted ahead of you). – Sergey Kalinichenko Apr 19 '12 at 14:42

Ralf de Kleine · Answer 2 · 2012-04-19T14:52:13.977

0

That would be the following query

DBCC CHECKIDENT ('[TableName]', NORESEED )

edited Apr 19 '12 at 14:52

answered Apr 19 '12 at 14:41

Ralf de Kleine

11,464
5
45
87

You should include the NORESEED parameter; otherwise this will also potentially reseed the identity value (in the event that the current identity and the max identity value are different). – MartW Apr 19 '12 at 14:47

score 0 · Answer 3 · answered Apr 19 '12 at 14:49

Just in case the increment is not the regular "1":

SELECT IDENT_CURRENT('mytable') + IDENT_INCR('mytable')

Of course, all these rely on the identity column having been populated consistently, ie no messing with manual inserts or reseeding. And Curt has mentioned the possible concurrency issue if you're going to use this ID for your insert directly.

Get latest identity, such as ID, from an empty table SQL

3 Answers3