DBCC CHECKIDENT does not reset identity value as you need?

DBCC CHECKIDENT is used for checking the current identity value in a table and can be used for changing and resetting the current identity value too. Sometime, resetting the current identity value with RESEED option makes developers confused (once it confused me too :)) because it does not always use the given value as current identity value for next insert statement. For example, below code is for resetting the current identity value of Table1 as 0.

DBCC CHECKIDENT (‘Table1’, RESEED, 0)

SQL Server will use the value ‘0’ as the identity value for next insert statement if:

• No rows have been inserted to the table since it was created
• Records have been deleted using TRUNCATE statement

Otherwise, identity value for next insert will be 0 + increment, in this case, if the increment has been set as 1, value 1 will be used as the identity value for next insert.