How to reset the IDENTITY values?

• sharma-691993

  SSCrazy

  Points: 2194

  More actions

  July 5, 2008 at 4:46 am

  #189207

  Hi

  In a table, i am having identity values of 200.

  I am deleting all the records.

  I need to insert values from 1.

  Without droping table, how to starts from 1.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 5, 2008 at 5:53 am

  #838486

  Use the TRUNCATE TABLE command

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  July 7, 2008 at 5:52 am

  #838777

  Hi Sharma,

  If you are deleteing all the records then TRUNCATE is the Best option.

  But If you want to change the IDENTITY value without deleting records then it can be done by two ways:

  First Method:

  DBCC CHECKIDENT ('TableName', RESEED, Integer) -- Integer means any value like 200

  GO

  Second Method:

  SET IDENTITY_INSERT ON

  GO

  --Now insert your required value

  SET IDENTITY_INSERT OFF

  GO

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers