Introduction:
During application development, we input dummy data into database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the Truncate command. This will delete data from table and also reset the identity column value to
Solution:
Truncate table [table_name]
for example:
Truncate table ColonyName
But the Truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.
In this case, first you need to delete data from the child and the master table.
After that, execute this command and it will reset your identity column to 0.
DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example
DBCC CHECKIDENT('ColonyName', RESEED, 0)
Note:
In case of, If you want reset identity value with no data loss:
No Truncate command is used.
Execute following command:
DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example
Here new_reseed_value should be the last identity column value:
here ColonyID is identity Column and I want reseed the value of this column using the following command:
DBCC CHECKIDENT('ColonyName', RESEED, 5)
Reset Identity column, Hide or Show the Solution File in Solution Explorer
During application development, we input dummy data into database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the Truncate command. This will delete data from table and also reset the identity column value to
0
.Solution:
Truncate table [table_name]
for example:
Truncate table ColonyName
But the Truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.
In this case, first you need to delete data from the child and the master table.
After that, execute this command and it will reset your identity column to 0.
DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example
DBCC CHECKIDENT('ColonyName', RESEED, 0)
Note:
In case of, If you want reset identity value with no data loss:
No Truncate command is used.
Execute following command:
DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example
Here new_reseed_value should be the last identity column value:
here ColonyID is identity Column and I want reseed the value of this column using the following command:
DBCC CHECKIDENT('ColonyName', RESEED, 5)
Reset Identity column, Hide or Show the Solution File in Solution Explorer
2 comments
Click here for commentsNo more live link in this comments field
ReplyNo more live link in this comments field
ReplyConversion Conversion Emoticon Emoticon