How to find next identity

You might already be aware of this but I just want to emphasize that
IDENT_CURRENT() isn't intended to used in application code to anticipate the
next value that will be assigned. The actual value assigned might be
different due to can change due to concurrent inserts by other sessions.


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"ArunDhaJ" wrote in message

> It works like a charm...
>
> Thanks Tom.....
>
>
> Regards
> ArunDhaJ
 >> Stay informed about: How to find next identity 

Check out IDENT_CURRENT() in the BOL.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"ArunDhaJ" wrote in message

Hi All,
Is there any way to know the next identity number the server would
probably assign for the identity column?

I'm having a table say Student with an identity column as StudentId.
How to find the next Id value the system would assign for a new
insert?


Thanks in Advance
ArunDhaJ
 >> Stay informed about: How to find next identity 

IDENT_CURRENT() is the last identity generated, not the next one, and we
probably need to know this within the context of a specific table.

This will return the last identity inserted for the [Student] table, and by
adding the identity increment (it is not necessarily 1), we can know what the
next identity insert will 'probably' be.

select IDENT_CURRENT('Student') + IDENT_INCR( 'Student' )

However, I don't see why an application would need to predict the next
identity, and that might be reliable. What we typically need to know is what
was the last identity inserted withinin the scope of our current session.

SCOPE_IDENTITY( )


"Tom Moreau" wrote:

> Check out IDENT_CURRENT() in the BOL.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "ArunDhaJ" wrote in message
>
> Hi All,
> Is there any way to know the next identity number the server would
> probably assign for the identity column?
>
> I'm having a table say Student with an identity column as StudentId.
> How to find the next Id value the system would assign for a new
> insert?
>
>
> Thanks in Advance
> ArunDhaJ
>
>
 >> Stay informed about: How to find next identity 

Why do you need to know the next value *before* you assign it (e.g. just
INSERT the row already)? You know that all bets are off if you wait before
inserting, right? Between the time you check and the time you actually
insert, 10 other values may have been assigned (or attempted and rolled
back), meaning the value you provided initially is no longer correct and not
the value that they will actually obtain.


On 8/1/08 6:58 AM, in article
77bef41f-b2a8-420f-af19-e676565d0c00.DeleteThis@l33g2000pri.googlegroups.com,
"ArunDhaJ" wrote:

> Hi All,
> Is there any way to know the next identity number the server would
> probably assign for the identity column?
>
> I'm having a table say Student with an identity column as StudentId.
> How to find the next Id value the system would assign for a new
> insert?
>
>
> Thanks in Advance
> ArunDhaJ
 >> Stay informed about: How to find next identity 

> select IDENT_CURRENT('Student') + IDENT_INCR( 'Student' )

"probably" is right. Inserts that happen after this query but before the
actual INSERT takes place will ensure that a different value is actually
assigned. Rollbacks can spoil the party, too.
 >> Stay informed about: How to find next identity 

Yes, it called the Observer Effect or Schrödinger's integer. It has been well
documented that future indentity values are in a state of superposition, and
attempting to determine what your next identity will be in a multiuser system
almost guarantees that it will either be inserted by another user or just
mysteriously never materializes thus leaving gaps or nonsequential IDs in
your rowset.


"Aaron Bertrand [SQL Server MVP]" wrote:

> Why do you need to know the next value *before* you assign it (e.g. just
> INSERT the row already)? You know that all bets are off if you wait before
> inserting, right? Between the time you check and the time you actually
> insert, 10 other values may have been assigned (or attempted and rolled
> back), meaning the value you provided initially is no longer correct and not
> the value that they will actually obtain.
>
>
> On 8/1/08 6:58 AM, in article
> 77bef41f-b2a8-420f-af19-e676565d0c00.DeleteThis@l33g2000pri.googlegroups.com,
> "ArunDhaJ" wrote:
>
> > Hi All,
> > Is there any way to know the next identity number the server would
> > probably assign for the identity column?
> >
> > I'm having a table say Student with an identity column as StudentId.
> > How to find the next Id value the system would assign for a new
> > insert?
> >
> >
> > Thanks in Advance
> > ArunDhaJ
>
>
 >> Stay informed about: How to find next identity 

"Eric Russell" wrote in message

> Yes, it called the Observer Effect or Schrödinger's integer. It has been
> well
> documented that future indentity values are in a state of superposition,
> and
> attempting to determine what your next identity will be in a multiuser
> system
> almost guarantees that it will either be inserted by another user or just
> mysteriously never materializes thus leaving gaps or nonsequential IDs in
> your rowset.

So either you see sql as physics or as a physic? Interesting, as sql is as
popular as quantum mechanics

www.beyondsql.blogspot.com
 >> Stay informed about: How to find next identity 

Thanks all. Actually I was in need of this function for debugging an
issue, but not to use it in the program. The explanation for why not
to use in code was really awesome, it would be really useful in
future. Thanks all for your support.

Regards
ArunDhaJ
 >> Stay informed about: How to find next identity