Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Resetting the Index or ID of a table

0 views
Skip to first unread message

Oli

unread,
Jul 10, 2003, 6:34:37 AM7/10/03
to
When you have removed a number of rows from a table, even all of them
and then you start adding more rows, the auto number carries on from a
higher count, rather than resetting back to one. I understand why this
is, the necessity for it but I need to reset it. I know there is a
short SQL string which you can run, with the table name and it resets
the tables index or ID to start on the next number after the row with
the current highest ID or index.

Ray Higdon

unread,
Jul 10, 2003, 9:07:39 AM7/10/03
to
DBCC CHECKIDENT (tablename, RESEED, 1) Will start the ID back at 1.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dan Guzman

unread,
Jul 11, 2003, 9:37:15 AM7/11/03
to
To add to Ray's response, TRUNCATE TABLE will reset the identity and
remove all rows. This is not permitted if the table is referenced by a
foreign key constraint, though.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Oli" <oliver...@hotmail.com> wrote in message
news:49cb8331.0307...@posting.google.com...

0 new messages