how to auto arrange ID number in order after deleting a row in SQL management studio 2008. explained below?

Question

I put auto increment filed to 1. Added 5 times and deleted. After adding a new item, it begins with 6. How to begin again from 1? or after deleting 4th item from, 1,2,3,4,5 then adding another one, how to make that one 4, not 6 . All I want is order.

Answer 1

As a rule, you don't want to renumber...since you used autonumber, that column is likely a key for the row and thus may be carried in other related tables as a foreign key. If you want to have a simple sequential number for display purposes, use one of the techniques shown at http://sqlserverplanet.com/oracleequivalents/rownum-in-sql-server (I suggest using ROW_NUMBER)

Answer 2

1

Answer 3

Auto Increment or Identity columns are made to increase the counter value for every insert but not to decrease for every deletion.



To "reset" this behaviour that is if you wish to reset the counter to 1, use

DBCC CHECKIDENT( table_name, RESEED, new_reseed_value )

System Function to manually set a new current identity value for the identity column.



And to enable the inserts into this Identity column manually, use



SET IDENTITY_INSERT database_name . schema_name.table ON | OFF



-- In 'thoughts'...

Lonely Rogue.

Answer 4

when I get carry of an digital mail, I constantly respond returned. whether I ought to deliver a respond some days later, i will apoligize for being previous due and then supply my message :) I frequently get a respond. it is exciting getting a message, in spite of the incontrovertible fact that it is much greater exciting once you get a respond returned ^^ I prefer I had L's digital mail handle. Gosh, we could style returned forth concerning the bestest candies interior the international. and then we could undertaking one yet another at a sport of tennis on line or some thing! XD