Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Sql indentity

hey guys, i need an sql query to find the indentity, the hard part is when the table is empty it returning  value of 2

select IDENT_CURRENT('Customer') + IDENT_INCR ('Customer')

i am looking for the next indentity, if the table is empty return 1 and if 1 record is inserted the sql statement must return 2 and so on
0
JCWEBHOST
Asked:
JCWEBHOST
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should NOT try to "see" the next identity, at least not to display/use that.
only doing the insert, and then fetching the value using SCOPE_IDENTITY() is the correct (for multi.user apps anyhow) way to do
0
 
JCWEBHOSTAuthor Commented:
give an exsample please
0
 
cyberkiwiCommented:
Quote from books online: http://msdn.microsoft.com/en-us/library/ms175098.aspx
Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

There is no other way I know of to correctly predict the next value.
The only way I know of that is not multi-user safe is to

insert a record, get scope_identity
delete record
fire ident_reseed to "return" the value used

This is, however, a terrible idea and as stated, not multi-user safe - you may end up reseeding wrongly.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
after you run the INSERT (I presume you know how to do that), you run:

select scope_identity()

Open in new window

0
 
JCWEBHOSTAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now