Link to home
Start Free TrialLog in
Avatar of robfendergibson
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
ASKER CERTIFIED SOLUTION
Avatar of mohan_sekar
mohan_sekar
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aneesh
another option is to TRUNCATE the table instead of clearing the data and the resetting the identity seed

truncate table
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.
Avatar of robfendergibson
robfendergibson

ASKER

Thanks Mohan, going with yout solution as the first one nad worked fine.  Also thanks to other contributers.

best wishes, Rob