Find The Next Identity Number After just deleted Idenity Number
-
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...
-
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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.
-
Thank you Phil and Charles... After your excellant info i will abandon this idea...
cj
-
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 !!!** -
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?
-
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 !!!** -
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
-
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply