• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

How to get the next value of the primary key as a identity

Sometime, I may delete the last recorder, but still want to get the next value
  • 4
5 Solutions
there is no way to do this.  You really do not want to care what your primary key values are, which is why the "rules of relational database" include a rule that says that primary keys must not contain data. If you have questions about why let me know and I can tell you about systems I had to clean up because they violated this rule.

I was wrong - it can be done...

        'e1'  ,   RESEED  , 1    
 resets the identity on table e1 to 1 (next value will be 2)

I still recommend against it
What happens if you insert 10 values and someone deletes record 4? You still have a gap in the values. Using a DBCC command (some of which are regarded as back door operations for emergencies) to go back, use ID 4, and reset back to 10, would be timeconsuming and would have to lock the table to prevent a primary key violation.

Note that if you are doing a transaction, you can use

SELECT top 1 IDENT_CURRENT('e') from e1

to get the current maximum value and write code to avoid "wasting" an id.

And I still think it's a bad idea!  

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

sorry - I did not clarify my SQL...

SELECT top 1 IDENT_CURRENT('identity_columnname') from tablename

turbot_yuAuthor Commented:
when trying SELECT top 1 IDENT_CURRENT ('identity_groupid') from [group], it give null
when trying select max(groupid) from [group], it gives 6
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as said already by AaronAbend: DON'T

you can use the DBCC CHECKIDENT(yourtable, RESEED)  to reset the identity to the last value (so your next insert will take the next value), but don't try to specify a explicit value in normal circumstances.
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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