Find The Next Identity Number After just deleted Idenity Number

Question

Post reply

Find The Next Identity Number After just deleted Idenity Number

Chris James

Grasshopper

Points: 24
More actions
March 26, 2006 at 11:29 pm

#68199

OK, i delete a identity number field - the indentity number was 20 - the next identity number is 21 but what is showing as Max(ID) is 19...
is there any VB / VBA code that can draw out the next potential identity number - even if i deleted a number of Identity fields (back to 16 or 15)
Is this even possible...
pseudo code...
rst.Open "Select Max(ID) from tblToolVersionInfo", oConn
this gives the existing MaxID but i need the next potential identity number regardless of how many have been deleted before it is generated...
hope that's clear...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Once you have the 'next' identity number, what are you going to do with it?

Maybe @@IDENTITY, or Scope_Identity() (check BOL for details) will give you what you need?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Charles Wannall Ten Centuries Points: 1023 More actions · Answer 2

Phil's question is the right one: Once you have the 'next' identity number, what are you going to do with it?

Typically, use IDENT columns when you want a blind, unique numeric key. If you care about the value of the column, other than to retrieve a row out of a table by filtering for it, you may be using the wrong kind of column for your purpose.

The obvious thing that comes to mind is that you might need to use a table of your own, where you store the Next Number. When you create a new record, you retrieve your own next number, apply it, and then increment the Next Number. Obviously, your Next Number value is always available for viewing until you actually increment it. I don't know of any similar ability with an IDENT column.

If you say more about what you're after, you'll probably get more replies. The easier you make it to understand what you're after, the more help you're likely to get.

Good luck.

Chris James Grasshopper Points: 24 More actions · Answer 3

Thank you Phil and Charles... After your excellant info i will abandon this idea...

cj

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

If I understand the question correctly then DBCC CHECKIDENT ('tblName') is what you're looking for...I needed to use this recently because I wanted to reseed a column.....

**ASCII stupid question, get a stupid ANSI !!!**

Charles Wannall Ten Centuries Points: 1023 More actions · Answer 5

Sushila, thanks for the info. I never ran across CHECKIDENT.

I did a quick run to test my theory of what it does. Of course it returns the value of the last ident value generated, even if the row with that value has been deleted from the table. For what the original poster had in mind, it's probably all that's needed.

I don't see a way to get the next ident value. This is probably not much of a consideration, but technically if you don't have the increment value handy you still don't know the next value. I looked around for a while, figuring there must be a way to get this using T-SQL, but I didn't see a method. Do you know of any? Or maybe through a table object from within VB or C?

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

Not that I know of...BOL does say "Many DBCC commands can produce output in tabular form...This information can be loaded into a table for further use" - but I don't know how...

**ASCII stupid question, get a stupid ANSI !!!**

John Laska SSC-Addicted Points: 454 More actions · Answer 7

select ident_current('Mytable') as CurrentID

Will get you the last identity value generated for a table.

If you want the next id use:

select (ident_current('MyTable') + IDENT_INCR('MyTable')) as NextID

Both will produce the result in a nice tabular format. Granted, it's a 1x1 table

Though, in all, I would avoid mucking about with identity columns manually, as you can start getting key violations and throwing errors.

One thing to keep in mind when working with identity columns, they just keep counting up. Whenever a row is inserted, the current identity will increment by the increment value. Normally, there is no reason to alter this behaviour. If you are concerned about having a contigious ID column, and identity field is probably not what you want to do.

In answer to the question that spawned this thread:

SELECT TOP 1 [MyId] FROM MyTable ORDER BY [MyId] DESC

Charles Wannall Ten Centuries Points: 1023 More actions · Answer 8

I think that your line with IDENT_INCR is actually the answer to the original post:

select (ident_current('MyTable') + IDENT_INCR('MyTable')) as NextID

That tells him the NEXT ident value, as desired.

For my part, no plans here to muck around with IDENT fields, just curious to know how to get the value requested, and you supplied the answer.

Thanks.