Solved

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

Posted on 2009-05-18
4
921 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2005 - Memory Table Column Names 11 80
Strange msg in the SSMS pane 13 60
My Query is not giving correct result. Please help 5 53
Substring works but need to tweak it 14 31
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 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