Solved

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

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get row count of current SQL query 8 44
Why do I get extra rows when I do inner join? 12 37
Restrict result set 1 33
MS SQLK Server multi-part identifier cannot be bound 5 25
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now