robfendergibson
asked on
Reset table autonumber with Microsoft SQL Management Studio Express (SQL 2005)
Hi Experts,
Excuse my ignorance on this one. I have an SQL 2005 database and using Studio Express to look at the data etc. I have cleared all the data from the tables but cannot see how to reset the autonumbers in each table back to 1. Is there a simple way of doing this? With Access I use 'Compact and Repair' - is there something similar with Studio Express?
Many thanks, Rob
Excuse my ignorance on this one. I have an SQL 2005 database and using Studio Express to look at the data etc. I have cleared all the data from the tables but cannot see how to reset the autonumbers in each table back to 1. Is there a simple way of doing this? With Access I use 'Compact and Repair' - is there something similar with Studio Express?
Many thanks, Rob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or, if you issue a truncate table when deleting all records it will reset the identity value as well as deleting the data. This however, will not work on tables referenced by foreign keys.
ASKER
Thanks Mohan, going with yout solution as the first one nad worked fine. Also thanks to other contributers.
best wishes, Rob
best wishes, Rob
truncate table