Solved

Reset table autonumber with Microsoft SQL Management Studio Express (SQL 2005)

Posted on 2009-05-18
4
920 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:robfendergibson
4 Comments
 
LVL 15

Accepted Solution

by:
mohan_sekar earned 125 total points
ID: 24415240
DBCC CHECKIDENT (tblName, RESEED, 0)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24415365
another option is to TRUNCATE the table instead of clearing the data and the resetting the identity seed

truncate table
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24415371
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.
0
 

Author Closing Comment

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

best wishes, Rob
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Restore Script - Syntax Error 8 104
How to simplify my SQL statement? 14 55
Update a summary table with values from detail records 6 29
tempdb log keep growing 7 33
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question