identity field | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

identity field

is there a way to keep an identity field, but if delete records, adjust the identity field based on the total records in the table?
to set the field’s next identity value dbcc checkident( ‘table’ ) you’ll probably need to be the owner of the object, dbo or sa to call this though… (not sure if it can be done within a proc and permissions then delegated…
if you want to remove gaps, then you’d have to write your own proc for this and use set identity_insert on/off to allow you to update the identity fields (which probably defeats the purpase of having the identity field Cheers