Is it possible to 'reset' the value of an identity column? 2

[redlam]

Is it possible to 'reset' the value of an identity column based on values in another column? Here's an example of the type of data I'm working with and what I'd like to do:
AutoID TeamID
----------- -----------
1 0
2 0
3 0
1 1
2 1
3 1
1 2
2 2
3 2
1 3

Right now, the identity column 'AutoID' is seeded at 1 and incremented at 1 so it keeps a running total of the records. Is it possible to have this field 'reset' itself to 1 everytime the data in the 'TeamID' column changes?

 

[TomSark]

The only way to reset an identity value back to it's original value is to truncate the table.

You can always SET IDENTITY INSERT OFF and force values into the table, but you will have to deal with possible collisions of values in your code if you do this...
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TomSark]

oops... some typos.

SET IDENTITY_INSERT <tablename> ON is the correct typing. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[foxdev]

The only way to reset an identity value back to it's original value is to truncate the table.

Not entirely true; you can use DBCC CHECKIDENT to change the seed value. For example:

[tt]DBCC CHECKIDENT (MyTable, RESEED, 1)[/tt]

Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[TomSark]

Well.. OK, I could have not used 'only', but... If the value of new seed is less than the maximum value in the column, then error 2627 is generated on subsequent references to the table. So, yes, you could get away with this if you are reseeding to a value that is higher than the maximum value.

And, you could do this if it is lower than the maximum value, but you will get an error on any future references to the table.

Therefore, I would use this approach with great care because it is dangerous... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[redlam]

Right. I had actually looked into the re-seeding option and determined that for my particular situation it wouldn't work. Thanks for all of your suggestions tho!