How to reset Identity field to 1 1

[HouDog]

Hi all,
I have a table that has an Identity Field as the primary key. Originally, it was created with the original seed value = 1 and increment = 1. After I delete all the records in the table, how do I reset it so that the next record inserted starts back at 1 instead of where it last left off ??

JH.

 

[hmckillop]

Script your table in Query Analyser, then drop and recreate it, ensuring the initial value is 1.

Alternatively instead of deleting your table use "Truncate Table" this will reset the seed.

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[HouDog]

The Truncate Table worked like a charm. Thanks !

 

[MDXer]

If you have removed your tables records by using a delete rather than a truncate you can run the following to reseed the ident.

DBCC CHECKIDENT (MyTable, RESEED, 1)

However if you are going to delete all records from a table then truncate table is always the most efficient way to go.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[HouDog]

Hi, would I execute that from a command line? or can I put that line in a stored proc ?

 

[MDXer]

yes you can run it within a stored proc.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck