Find Next SQL AutoNumber

390 pts.
Tags:
SQL
SQL tables
Hi, I was wondering if there is a way to find the next autonumber in a SQL table before inserting the record? I tried just looking at the previous number and adding one but if I delete the last record then this wouldn't work or if I delete all the records then the number won't be 1. Any suggestions would be greatly appreciated. Thanks, Dustin

Answer Wiki

Thanks. We'll let you know when a new response is added.

The IDENT_CURRENT function will give you the most recently used identity. You can then use the IDENT_INCR to get the number by increment by (usually 1, but not always). Add them together and you’ll get the next value to be used.

However it is better to insert the new record then get the value of the record that was added. This way if two users poll the database at the same time, then insert records you don’t have to worry about both users thinking they have the same value. You can get the value after the record was inserted by using the SCOPE_IDENTITY function.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following