April 23, 2014 at 7:49 am
Greetings,
I have encountered an anomaly & was wondering if anyone knew what might be the cause. The dbcc checkident(reseed) command behaves differently on two SQL servers.
In both cases, I am deleting from (not truncating) data in two tables, due to foreign key constraints. (I am truncating other tables, the issue is not with those tables, only with the deleted-from tables.) On one server, I need to use dbcc checkident(reseed,0) so that when I insert fresh data, it begins with identity key #1. According to MS documentation, that appears to be the correct behavior, when data has been deleted from a table, rather than truncated.
However, on the other server, I need to use dbcc checkident(reseed,1); if I use ...(reseed,0) on that server, it begins inserting data with identity key #0.
This is consistent, repeatable behavior on both servers.
Thanks,
Randy
April 23, 2014 at 8:11 am
It has to do with if there have been rows inserted into a table since it was created. If you have tables that are new or truncated, you'll see the value. If you're working off a new table or a truncated table, it will be the value +1.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2014 at 8:14 am
That's why it seems odd. In both cases, this behavior is observed after a fresh build, i.e., after the TFSBuild process first DROPS the database, then constructs it afresh. So, it would seem that in both cases, it's an entirely fresh build. As I said, the process is repeatable, on both servers.
April 23, 2014 at 8:15 am
My last statement begs additional investigation. The DELETE/TRUNCATEs wouldn't be necessary on a fresh build.
April 23, 2014 at 8:19 am
You sure it's a complete rebuild? Nothing has inserted a row? It's not doing an incremental build? The behavior is pretty consistent from what I've seen. But then, if it's a complete rebuild, why do you need to reseed the identity value at all?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply