CODEWITHSUNDEEP
sqlprimary-key
Sergey
Sergey

Reputation: 11908

Resetting the id in table without dropping it

There are ten entries in the table and primary key id is set to autoincrement. If I delete all entries and insert one more its id will be 11, is it possible to reset ids to 1 again?

Upvotes: 0

Views: 1478

Answers (3)

Chris Chilvers
Chris Chilvers

Reputation: 6479

DBCC CHECKIDENT("table", RESEED, 1);

You can read more at msdn, DBCC CHECKIDENT (Transact-SQL).

Upvotes: 1

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

Try truncate table command like below : 

TRUNCATE TABLE tablename

it will reset auto increment id to 1.

If your table is refrenced with other table using foreign key then you can enable/disable contraint using below commands :

To Enable constraint: 

EXEC sp_msforeachtable @command1="print '?'", 
                       @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all";

To Disable constraint: 

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";

Upvotes: 4

Olli
Olli

Reputation: 752

ALTER TABLE table AUTO_INCREMENT = 1;

You can set any value for "1"

Upvotes: 0

Related Questions