2

Using SQL Server, I've got a column "ID" in my table that auto-increments every time I insert a new record.

How can I reset this counter and return to 1?

AHiggins
  • 7,029
  • 6
  • 36
  • 54
img.simone
  • 632
  • 6
  • 10
  • 23
  • why are you trying to do this? the whole point of the ID is being unique........ – Bart Hofma Nov 14 '14 at 13:17
  • 1
    possible duplicate of [How to reset AUTO\_INCREMENT in MySQL?](http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) – Igor Milla Nov 14 '14 at 13:17
  • @BartHofma possible example: you delete all rows from the table, and you want new rows to get id's starting from 1 – Igor Milla Nov 14 '14 at 13:19
  • Tagging this with only `counter` is really not clever. – CBroe Nov 14 '14 at 13:26
  • ah, i got confused because he used "every time" so i thought he wanted to do it for each record. In your case the linked page should help – Bart Hofma Nov 14 '14 at 13:36
  • As an advice, never use auto-increment columns for ID. Instead on inserting values into the table use a procedure and you take care of the ID . For example set @ID = isnull(Max(ID),0)+1 from TABLE<-- this is a sort of replication for the auto-increment, but more eficient from my point of view – CiucaS Nov 14 '14 at 13:50
  • @CiucaS: _“this is a sort of replication for the auto-increment, but more eficient from my point of view”_ – and you are basing this statement on what exactly? – CBroe Nov 14 '14 at 23:27
  • @CBroe based on experience. – CiucaS Nov 15 '14 at 10:12
  • @CiucaS: That is quite vague; without any actual prove I don’t think that statement can be supported. – CBroe Nov 15 '14 at 12:25
  • @CBroe the differnce is that you don't have toreset the identity after every delete and also that will allow you to add your own values if needed. – CiucaS Nov 17 '14 at 12:47
  • @CiucaS: Auto-increment IDs _don’t_ get reset, and the usage of “own” values is normally also not a use case. – CBroe Nov 17 '14 at 17:27
  • Suggested Duplicate goes to MySQL answer, not SQL Server ... – AHiggins Nov 19 '14 at 15:11

1 Answers1

1

If you are SQL Server, and assuming your ID column uses the IDENTITY() property, you need to use the DBCC CHECKIDENT syntax:

USE MyDatabase
GO

DBCC CHECKIDENT ('myschema.MyTable', RESEED, 1); 
GO
AHiggins
  • 7,029
  • 6
  • 36
  • 54