SQL Query

Question : If a column as has been declared as a primary key with auto increment(IDENTITY(1,1) and it keeps on incrementing when a record is added what can can I do to reinitialise the seed to start at 1 again after deleting all the records in a table.

Answer  1:

You can use the DBCC function or Truncate the table.

 DBCC CHECKIDENT ( <table name>,RESEED,<new value>)

 Truncate Table <table name>

Answer  2:

Here is a simple sample for the question:

--Drop the foreign key

ALTER TABLE dbo.CategoryTable1Sub

DROP CONSTRAINT FK_CategoryID

GO

truncate table dbo.CategoryTable1

truncate table dbo.CategoryTable1Sub

GO

--Add Foreign key back

ALTER TABLE dbo.CategoryTable1Sub ADD CONSTRAINT

FK_CategoryID FOREIGN KEY

( CatID )REFERENCES dbo.CategoryTable1

( Category_ID )

GO

Answer  3:

f you are not able to TRUNCATE, and the issues of removing the CONSTRAINTS are burdensome, as Ken indicated, you may wish to try using the DBCC RESEED functionality.

 

Something like this:

 

DBCC CHECKIDENT ("YourTable", RESEED, 1);.