I would like to have a nice template for doing this in development.
A:
TRUNCATE table
will reset the value to its original seed.
@Darren Kopp: aye smacks forhead
hometoast
2008-08-19 21:17:02
+1
A:
To set the identity to 100:
DBCC CHECKIDENT (MyTable, RESEED, 100)
Aww snap!
Keith
2008-08-19 21:21:30
This will mean the next identity is 101 by the way - and so "0" to reset the next inserted to "1".
Kieren Johnstone
2010-07-09 07:53:49
@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
2010-07-09 11:55:42
+1
A:
@hometoast - it will also delete all the info from the table.... and won't put any info in the transaction log, so the only way you will get the information back, is with a full database backup.
Darren Kopp
2008-08-19 21:32:17
+9
A:
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
2008-08-19 21:40:01
Your warning was exactly what I was searching for. Glad to see someone mention it here.
TheTXI
2009-03-19 16:54:35