66

I would like to have a nice template for doing this in development. How do I reset an increment identity's starting value in SQL Server?

Mark
  • 2,041
  • 2
  • 18
  • 35
Joel Meador
  • 2,586
  • 2
  • 19
  • 24

3 Answers3

133
DBCC CHECKIDENT('TableName', RESEED, 0)
Ryan Sampson
  • 6,717
  • 12
  • 47
  • 55
36

Just a word of warning with:

DBCC CHECKIDENT (MyTable, RESEED, 0)

If you did not truncate the table, and the identity column is the PK, you will get an error when reaching pre-existing identites.

For example, you have identities (3,4,5) in the table already. You then reset the identity column to 1. After the identity 2 is inserted, the next insert will try to use the identity 3, which will fail.

Forgotten Semicolon
  • 13,909
  • 2
  • 51
  • 61
  • I don't know what truncating here is referring to, but if in doubt issue `select max(customerid) from customers` to see what the highest existing value is – Simon_Weaver Dec 11 '17 at 10:28
17

To set the identity to 100:

DBCC CHECKIDENT (MyTable, RESEED, 100)
Kiquenet
  • 14,494
  • 35
  • 148
  • 243
Keith
  • 150,284
  • 78
  • 298
  • 434
  • 8
    This will mean the next identity is 101 by the way - and so "0" to reset the next inserted to "1". – Kieren Johnstone Jul 09 '10 at 07:53
  • @Kieren Johnstone - yes it will continue the numbering from 100, as if record 100 has just been added and so the next is 101. – Keith Jul 09 '10 at 11:55