I've found that the best way is to do two commands:
DBCC CHECKIDENT (authors, RESEED, 0)
DBCC CHECKIDENT (authors, RESEED)
The first sets the auto-increment number to zero (then it counts up till
it finds an open number), and the second moves the auto-increment beyond
the largest existing value. Doing only one or the other wasn't as
good... if there are any gaps in your existing auto-increment values
then doing the first one alone will eventually cause primary key
violations (assuming the auto-increment field is a primary key)
Mark
-----Original Message-----
From: Dan G. Switzer, II [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 12, 2006 11:12 AM
To: CF-Talk
Subject: RE: compacting mssql tables.
Daniel,
>Hi there, rather than recreate a table I would like to compact it, so
>the increment seed counter refreshes starting at 1. Is there a way to
do this?
You've got a couple of options.
1) You can use "TRUNCATE TABLE authors" to wipe the table and restore
the identity seed.
2) Use "DBCC CHECKIDENT(authors, RESEED, 1)" to reset the seed to "1"
(or any number you want to reseed to.)
NOTE: The table name in the above examples is "authors".
If you use the DBCC CHECKINDENT w/RESEED option, just make sure you pick
a starting seed *greater than* the highest identity value in the table.
(If you've deleted all the rows, this isn't an issue.)
-Dan
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252968
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
