There are times when you want to re-seed or reset the value of an identity column in a table. when you delete a lot of records from a table and then try to insert more records. you would expect the identity values to start after the max value present after delete.. I have inserted records into a sql server database table. the table had a primary key defined and the auto increment identity seed is set to �yes�. this is done primarily because in sql azure, each table has to have a primary key and identity defined.. Dbcc checkident atomically change identity seed? check id exists in sql table? how to check there is no item in table? advertisement identity seed reset in sql table feb 16, 2004. i have a test database that is being moved to the production server. currently in one of the tables i have an identity seed for each record..
Execute dbcc checkident (table_name, reseed,new_reseed_value) with new_reseed_value set to the desired starting value. changing the seed value the seed value is the value inserted into an identity column for the very first row loaded into the table.. Hi pinal, i just tried you query and discovered that the object_id(), ident_seed(), ident_incr(), and ident_current() functions require the table name be prefixed with schema name, if the schema is something other than the default dbo.. Microsoft sql server's identity column generates sequential values for new records using a seed value. seeding and reseeding an identity column is easy and relatively safe, if you do it correctly..
