Home Forums Programming General DBCC CHECKIDENT behaving differently on empty tables RE: DBCC CHECKIDENT behaving differently on empty tables

  • From https://msdn.microsoft.com/en-us/library/ms176057(v=sql.110).aspx:

    If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.

    A quick aside: the documentation seems a bit inconsistent, as that's from the 2012 documentation (which correctly describes the behavior in 2012), but the most recent version suggests that the new_reseed_value+increment behavior applies only in 2008 R2 and earlier.

    At any rate, in your scenario, the trick is that T_B has never had any rows inserted, so it uses the new_reseed_value of 0.

    T_A has had rows inserted, so it uses new_reseed_value+increment value.

    If you switch the DELETE to a TRUNCATE TABLE, then as the documentation points out you would get the new_reseed_value of 0 for both.

    Cheers!

    EDIT: I'm actually not sure what the most recent documentation is getting at; hadn't tested this behavior in a long time, so I was only confident about behavior in 2012 and earlier, but a quick test on a 2016 instance gets me the same results as on 2012.