How to get the index to start at 0 ??

Hi,

How can I,after deleting all the records in a table, get the index to start at 1 again without having to drop the table and then create it again.

THANKS IN ADVANCE !

Indecies does not start at any number. There must be some misunderstanding.

sorry my mistake.

I have a table called DD_detail with a column DD_id of int, Identity, Identity Seed = 1, Identity Increment = 1.
and columns DD_type varchar etc....

when I delete all rows from this table, and start to insert again, DD_id starts at the last identity before the table was cleared.
I want it to start at 1 again. How do I do that without dropping the table and then creating it again ??

THANKS

You can change the identity value by using DBCC but I don't see what the benefit would be.

I saves me from doing a count everytime I want to see how many records there are in the table.
In mysql you simple run "repair table name" script and wallah, it's done.

Do you understand why I want this now ? Their must be a way.

THANKS

You cannot assume that the highest identity value in the table is the same as the number of records. If some insert fails there will be a gap for instance.

Hi Dieter,

You can actually follow this:
DBCC CHECKIDENT([table name],reseed,0)
-- this will automatically set your identity to 0 again.

I hope it works.

Thanks a MILLION !!!!!!!!!!!

Finally, it works !!!

THANKS THANKS THANKS THANKS !!!!!!

I try to use that command:

DBCC CHECKIDENT([table name],reseed,0)

Example:
DBCC CHECKIDENT(dbname.tablename, reseed, 0)

I end up getting the error -

"The Query Designer does not support the DBCC SQL construct."

P.S. I'm using SQL Server Standard Edition 8.00.818 (SP3)