Reset Auto-increment SQL Server

Question

Good morning,

I created a new table in Sqlserver and I am testing some scripts to popular the table and I came across the following situation, in my tests I am doing the Insert in the table checking the information and if they do not agree I delete the table, only when I do this the id field that is auto incremental is not zeroing, it is continuing from the id where it stopped, for example: If I include 10 records the auto-idincremental will be at 10 if I delete the records of the table and do another Insert it starts with 11 instead of starting with 1, someone can help me?

Answer 1

I simulated your question as follows

• I did the insert of 83 records in one table

• I deleted the records from the table

• I executed the command to list what value the AUTO_INCREMENT table

  SELECT IDENT_CURRENT('TABELA')

• I made a select to show that the table is without records

• I used this command with the table without records to reset the auto-incremental

  DBCC CHECKIDENT (TABELA, RESEED, 0)

• I listed what current value of AUTO_INCREMENT of the table and zero

• Alternatively you run this command via cmd

  OSQL -E -S <SERVIDOR> -d <BASE> -Q "DBCC CHECKIDENT('<TABELA>', RESEED, 0)"