December 5, 2016 at 7:20 am
It's not that important other than for my own curiosity, but does anybody know why this code
Create Table T_A
(
AID Int Identity (1, 1),
A Varchar (10)
)
GO
Create Table T_B
(
BID Int Identity (1, 1),
B Varchar (10)
)
GO
Insert Into T_A (A)
Values ('A')
Delete T_A
DBCC CHECKIDENT ('T_A', RESEED, 0)
DBCC CHECKIDENT ('T_B', RESEED, 0)
Insert Into T_A (A)
Values ('A')
Insert Into T_B (B)
Values ('B')
Select * From T_A
Select * From T_B
Drop Table T_A
Drop Table T_B
gives
1,A
0,B
December 5, 2016 at 9:11 am
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.
December 5, 2016 at 9:32 am
That's it!
It's also bonkers!
Why would they implement such behaviour?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply