resetting IDENTITY column

[k108]

Hi,

Is there a way to re-set the values in an identity column w/o dropping and recreating the table?

That is, I would like to start the identity column numbers at 1.

Thank you

 

[amrita418]

Truncate table Tablename

That will reset the identity to 1.

Regards,
AA

 

[sunila7]

Another option is:

DBCC CHECKIDENT (TBLName, RESEED, 1)


Sunil

 

[k108]

Thanks for the info. Interestingly, I just read something about the TRUNCATE command. It said that if you issue a TRUNCATE command, it invalidates the the transaction log for the entire database, and can't be backed up until the next full database backup!

I have never heard of such a thing (not in Oracle at least).

This seems pretty lame to me. But what do I know.

 

[TysonLPrice]

If you want to start at 1 use:

DBCC CHECKIDENT (TysonTest, RESEED, 0)

Using DBCC CHECKIDENT (TysonTest, RESEED, 1)

will make the identity in the next insert into an empty table 2.