sql - Reseeding Identity Column to Original Value -

sql - Reseeding Identity Column to Original Value -

is there way reseed identity column it's original value? have identity(1,1) table definition. i've tried reseeding using method:

dbcc checkident (table1, reseed, 1)

but noticed, sql reseeds current seed + new seed value, when insert new rows starts auto-increment count @ 2 instead of 1. i've tried making new reseed value 0, rows insert after end beingness 1 , column doesn't auto-increment anymore.

by way, if helps, need column restart auto-increment count @ 0 because need count restart every month. have trigger after insert set this, reseeding giving me trouble.

the next statement:

dbcc checkident('yourtablename', reseed, 1);

tells database akin to:

"hey database! highest identity value in the yourtablename table #1, next inserted record should utilize identity value #2."

this explains why table starts identity value 2 when reseed using statement.

more microsoft's help dbcc checkident() indicates:

current identity value set new_reseed_value. if no rows have been inserted table since created, or rows have been removed using truncate table statement, first row inserted after run dbcc checkident uses new_reseed_value identity. otherwise, next row inserted uses new_reseed_value + current increment value.

if table not empty, setting identity value number less maximum value in identity column can result in 1 of next conditions:

if primary key or unique constraint exists on identity column, error message 2627 generated on later insert operations table because generated identity value conflict existing values. if primary key or unique constraint not exist, later insert operations result in duplicate identity values.

it sounds want alter table's initial starting seed value 1 0. in mind, need modify table's definition use:

identity(0,1);

i hope helps. luck.

sql sql-server auto-increment identity-column