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

turbot_yu
turbot_yu used Ask the Experts™
on
Sometime, I may delete the last recorder, but still want to get the next value
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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...

DBCC CHECKIDENT
(
        '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!  


sorry - I did not clarify my SQL...


SELECT top 1 IDENT_CURRENT('identity_columnname') from tablename

Author

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 Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial