Reset Primary Key Column

Question

hi

i have a table named users which have a ID column as primary key(is identity=true).

when i add some data to table everything is ok but when delete some of them,

the deleted IDs cause Irregularity in ID column.i want to reset the ID column to begin from 1

and increment 1 per each data without remove existing data.

for example:

before delete : ID 1 2 3 4 5 6 7 8 9

after delete : ID 1 2 4 5 8 9

what should i do???

thanks

Jignesh Trivedi · Answer

No does not delete any record from the table. it just rest running identity value. one more point you cannot update column value which define as Identity. hope this will help you.

Jignesh Trivedi · Answer

Hi Update column value in identity column is not possible Agree with Rahul solution this is one way... If Id column is foreign key for other table truncate table is not work... in this case you can use DBCC CHECKIDENT command check following link https://msdn.microsoft.com/en-us/library/ms176057.aspx hope this will help you.

Vikram Agrawal · Answer

Hi Abolfazl as per your requirement you need to update ID column after each deletion. You need to write little logic for this in the Delte SP. Thanks

Rizwan Ali · Answer

when you use the delete command then only table data is deleted not structure so id column always will be started again with the last value but when you use the truncate command then it will be start again from 1 and one thing is also noticeable that use identity(11) for increment of 1 every time when you add a new value in the table

Rahul Bansal · Answer

Always use the identity with primary key like this create table table1 ( ID int PRIMARY key IDENTITY(11) Name varchar(10) ) In (11)  first 1 is starting value and second 1 means the incremental value. To reset the identity column use truncate table table1 which will delete all data of your table. copy the data of the table into another table before truncate the table and then move the data.

Abolfazl · Answer

thanks for your guidance. i used DBCC CHECKIDENT but it delete all data in table i don't want it. i'm looking for a way that reset ID column without delete my data maybe i should use a temp table and store my data before use DBCC CHECKIDENT as Rahul said. Vikram Agrawal can you tell me how to update id column? thanks