November 12, 2015 at 7:38 am
Hi,
Hopefully there are some people still monitoring this SQL Server 2005 forum...
I've run across a strange issue, that I've tried many Google searches for, but can't seem to find anything related. I have a SQL 2005 table with an identity column. This table gets inserts to it, obviously bumping up the IC count, and then we have a job that pulls the data from the table and deletes the rows it pulled. Therefore, most of the time, the table is actually empty, but the next insert continues on with the correct next IC. What I've had happen a few times now, is that when I've made a change to the table design, and saved the change, it seems to reset the IC count back to it's configured seed value. I know it's going back to the configured seed value because the last time this occurred, in order to get the IC value back to what it should be, I changed the seed value of the column. Then this last time, it was sitting at that new seed value after the table modification. Very strange behavior...
So, I'm wondering if anyone else has experience this sort of behavior with identity columns, and if so, what could be done to prevent it. This is causing us a problem because we use the IC value in the table we're pulling this data into, and we're expecting it to be a unique value. When it gets reset, and the pull occurs, it causes a duplicate row error in the job.
I did try to duplicate it, on the same server, by creating a test table with a IC column and a data column, then adding some data so that the IC values are incremented a few times, and then adding a new column and saving. That seemed to work as expected. Then I deleted the rows (not truncate table, just delete as we do in our job), added another column to the table, and entered a new row, but it still acted as expected with the new row picking up where the IC left off.
The only thing I haven't tried is to create another test table and create a foreign key between them, like we have in the real table situation, but I don't know how that would relate to issues with the identity column value when the table is modified.
Thanks,
Larry
November 12, 2015 at 7:48 am
Let me guess, you use the SSMS GUI table designer to make the change?
The table designer creates a new table with the new schema, moves the data across and drops the old table. Hence the identity could reset, it's a new table.
Don't use the table designer, write your table schema changes as ALTER TABLE statements and you won't have this problem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply