CODEWITHSUNDEEP
sql
Franklin Joseph Moormann
Franklin Joseph Moormann

Reputation: 71

identity id column in sql table doesn't start at 1

I ran a delete script to delete all rows that had an id greater than 0 to clear the table and now when I try to add rows to the table, the id starts off where it left off from before. How can I delete the info in the table so the id starts off at 1 again?

Upvotes: 1

Views: 6062

Answers (3)

AjV Jsy
AjV Jsy

Reputation: 6075

In MS-SQL you can have a play with DBCC CHECKIDENT (yourtable, reseed, 0)

Upvotes: 0

Swagata
Swagata

Reputation: 622

I don't think that is possible in the same table. Which database are you using? In previous versions of MS Access compacting the database used to work but not any more. I don't think SQL Server allows that either. The only way is to copy the records from this table to a new table, delete the old table and rename the new table to the old table name.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

Delete statement doesnt reset the identity value. Use Truncate table command if you want identity value to be reset. something like this..

TRUNCATE TABLE Table_Name

This will empty the table and reset the identity value.

Only use TRUNCATE when you want all the rows to be deleted. As it does not allow you to use WHERE clause.

Upvotes: 5

Related Questions