SQL reseeding works but auto increment starts at 0

Answer 1

Using that command, you're telling the IDENTITY to set itself back to 0 as its new seed.

It will not go back to the original definition (IDENTITY(1,1)), but to the value you specify as the third parameter in the DBCC command.

If you want to go back to using a 1 as your seed value, use:

DBCC CHECKIDENT(MyTable, RESEED, 1)

If you want to go to 100, use:

DBCC CHECKIDENT(MyTable, RESEED, 100)

That value that you defined and set by using DBCC CHECKIDENT will then be the first new value used for the IDENTITY column when you insert a row into that table.

When you check the MSDN Books Online documentation, you can see:

DBCC CHECKIDENT 
( 
    table_name
        [ , { NORESEED | { RESEED [ ,new_reseed_value ] } } ]
)

new_reseed_value

Is the new value to use as the current value of the identity column.

So it's really you who defines the new value of the IDENTITY column - if you pass in 0 as you do in your post, it will be 0 - that's what you asked for, after all...

Answer 2

If you don't need logging, you could issue the TRUNCATE TABLE statement. It would delete the records and start the identity back to the original identity seed.

Answer 3

This question really hasnt been answered, however I have come to the conclusino that since this will be a rare event (reseeding tables) for my database. It is not that important at this time.