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 |
|
edyl
Starting Member
35 Posts |
 Posted - 2011-08-04 : 17:07:51
|
| Hello Everyone, I am populating a table in increments which has a Primary Key as an Identity Column. The table currently has about 400K rows. The rows that were inserted after 200100th row had some problems and I deleted them in bulks. I deleted about 100K rows and then picked up the insertion again with good data. However when the good data were inserted I noticed that the count values for identity PK did not pick up from where it was left. So the identity PK value for the next row after 200100th row instead of being 200101 is now 300050. I believe that is because the identity counter is still accommodating for 100K some rows that were deleted earlier. Without noticing it sooner, I went on inserting another 200K rows of good data. The table now has about 400K rows but the PK value for the last row in the table is 550120. Is there a way to readjust the PK values for the rows after the 200100th value so the count of row s in table and the identity PK value for the last row correspond? Is there a way to prevent this from happening in the future?Any suggestion, any recommendation greatly appreciated.Thanks in Advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
edyl
Starting Member
35 Posts |
Posted - 2011-08-05 : 09:37:54
|
Thanks. quote:
don't delete data, mark the row for deletion instead
Are you indicating to use some sort of delete flag?Regards. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
edyl
Starting Member
35 Posts |
Posted - 2011-08-05 : 16:21:40
|
| Thanks for your insights. I am ignoring the gaps and hoping the DBAs won't scratch their heads...:-D. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
resetting the identity column values