Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
patshaw
Posting Yak Master
177 Posts |
 Posted - 2009-04-03 : 16:57:38
|
| Hi,I have a table that every day is populated, updated through the day and then the data is deleted the following day. Once deleted, the same process begins again. Before the delete occurs, the data is copied into an archive table so there is a permanent replica of all the data that has come through the system.For each row that is inserted into the archive table, the primary key is maintained and held as the primary key in the archive table but without the identity property. The insert code will only insert rows from the live table where the PK does not exist in the archive table.I noticed today that for a couple of weeks no data has been being inserted to the archive table, even though the job has been showing as successfull. When I investigated the issue, the reason is because the primary key values in the live table already exist in the archive table. On further inspection, this appears to be because the live table has been re-seeded.There have been no TRUNCATE statements issued against the table. The only thing I can think of is that a couple of weeks ago I added a new column through Management Studio instead of using an ALTER TABLE ADD COLUMN command. Now, I know that when you add a column through MS it rebuilds the entire table but didn't think for a second that it may perform a re-seed! Is this possible? |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-03 : 17:30:23
|
| I don't think adding a column will reset the seed. |
 |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2009-04-03 : 17:48:02
|
| I wouldn't have thought so either, but when you script the code that is generated by MS for adding a column is literally rebuilds the table. This is the only logical explanation I can give to what has happened on the table...... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2009-04-04 : 07:55:40
|
| Thanks Tara. I'll put on my ivestigative hat... |
|
|
|
|
|
|
|
|
Can adding a column through MS force a reseed?