How To Reorder Auto Increment Column Values, After Deleting Any Row Other Than Last Row?

I want to know is there any SQL query for asp.net,c# that can just re arrange auto increment coloumn values.. eg. deleting 2 in the table: sno 1 2 3 4 does: sno 1 3 4 but i want

Solution 1:

Let your table be named Parent and table that will hold the backup is called Backup. They should have identical columns.

INSERTINTO dbo.Backup 
    SELECT*FROM dbo.Parent

Now truncate the parent table

TRUNCATETABLE dbo.Parent

Now you can just insert the data back using the first command and just reversing the table names.

Remember that this may not work in all cases. You may have On delete cascade on and if that is the case, then you would loose all data from other tables also which are referencing the parent table. I think that you should never use this is you are using any Foriegn Key reference on this table.

Solution 2:

Following are the queries which should run 1 after other to get this functionality done. This can be easily achieved in C# by executing a generic ExecuteNonQuery().

DELETEFROM TBL1 WHERE sno =@sno; 

UPDATE TBL1
SET sno = sno -1WHERE sno >@sno;