July 20, 2017 at 10:42 am
Hi,
There is an user table which uses for eventlogging. The id column is Primary and Identity(1,1). The app team deletes data from it and hence the ID value is not synchronous.
What is the best approach to reuse the old values.
Or do you suggest creating a new column with Identity(1,1) and then dropping the main primary key column which is Identity(1,1) later on? But this approach has an issue, a table does not offer two identity values. Moreover, this will be treated like a recurrent task , so need to find a suitable way to automate this effort, may be through a job it will work weekly/monthly.
Suggestions and advises plz
Thanks.
July 20, 2017 at 11:23 am
SQL-DBA-01 - Thursday, July 20, 2017 10:42 AMHi,There is an user table which uses for eventlogging. The id column is Primary and Identity(1,1). The app team deletes data from it and hence the ID value is not synchronous.
What is the best approach to reuse the old values.
Or do you suggest creating a new column with Identity(1,1) and then dropping the main primary key column which is Identity(1,1) later on? But this approach has an issue, a table does not offer two identity values. Moreover, this will be treated like a recurrent task , so need to find a suitable way to automate this effort, may be through a job it will work weekly/monthly.
Suggestions and advises plz
Advice: leave it alone and ignore the gaps.
Not sure what you mean by synchronous, please explain.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 20, 2017 at 11:54 am
Can not leave it alone. As the ID numbers are about to cross BigInt limit.
Thanks.
July 20, 2017 at 12:01 pm
ALTER TABLE tableName ALTER COLUMN ID bigint
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 20, 2017 at 12:10 pm
This is not the question.
Lets say the Id column has 25,000 entries. Say, first 1-10 records of ID values between 1-100. 11-100 is somewhere between 257000- 289000 like this. So the ID column values are not in sequence. the Id column is already in BigInt (has been changed to fit more width from Int). Now it will he helpful to reseed the Id values so that the 25,000 record values will be in sequence. At the top I mentioned Option #2, is that going to be possible to implement smoothly?
Thanks.
July 20, 2017 at 12:26 pm
The best approach (from my understanding) is to not force reuse of old values. The primary key, for best performance, should be an ever increasing value.
Why do you need to keep the value synchronous?
If you really wanted to re-use old values, you could insert the entire table into a temp table excluding the identity column, truncate the table and then re-insert the data. Truncate will reset the seed.
Lets say you have 100, 101 and 102 as your ID, next seed is 103. The app team deletes ID 101. Your table now has 100 and 102. You reset the seed to be 101 and the app team does an insert. your table now has 100, 101 and 102 again. Now your app team does an insert. Does the insert succeed? What value is the seed at?
Where I work, we don't reset the seed. If the data is cleared out and re-inserted at regular intervals, we truncate the table before inserting. If the data is NOT cleared out at regular intervals, what is the datatype of the ID? Would changing it to a different datatype (bigint for example) resolve the problem?
It does depend on the number of rows in the table, the expected table growth and what your ID is actually being used for. Does the ID column serve a purpose? If not, could you just drop it?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 20, 2017 at 1:09 pm
Here's one option before going to bigint.
DBCC CHECKIDENT ( 'YourTable', RESEED, -2147483648)
July 20, 2017 at 1:37 pm
I would advise leaving it alone and not trying to reuse the gaps. Doing so is asking for trouble.
If, say, you had the values 10, 20 and 35 in the table and you reseeded the identity to 11, then it will happily go and put 11, 12, 13 into the table, 18, 19 and 20, at which point you have a primary key violation occur.
Leave it alone. If it's bigint, you won't run into the maximum in any reasonable time frame.
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
July 20, 2017 at 1:47 pm
I used the below approach. For testing I created another mimic table and tried. It is working. But for large table it will be a mess , I assume.
1. Create a temp table with the same structure
2. Import values from table
3. Remove constraints from tmp table
4. Drop column id from tmp table.
5. Add a new ID column with Identity(1,1) property
6. Drop main table
7. Rename the table to the intended table name.
8. Add back the constraint (Primary Key)
9. Check the results.
Thanks.
July 20, 2017 at 1:59 pm
SQL-DBA-01 - Thursday, July 20, 2017 1:47 PMI used the below approach. For testing I created another mimic table and tried. It is working. But for large table it will be a mess , I assume.
1. Create a temp table with the same structure
2. Import values from table
3. Remove constraints from tmp table
4. Drop column id from tmp table.
5. Add a new ID column with Identity(1,1) property
6. Drop main table
7. Rename the table to the intended table name.
8. Add back the constraint (Primary Key)
9. Check the results.
What is the current maximum value of the identity column in your table?
July 20, 2017 at 2:04 pm
2377688866
Thanks.
July 20, 2017 at 2:12 pm
SQL-DBA-01 - Thursday, July 20, 2017 2:04 PM2377688866
Based on this comment
Id column is already in BigInt (has been changed to fit more width from Int)
You have 9,223,372,036,854,775,807 - 2,377,688,866 = 9.2 * 10^18 identities remaining.
Should be OK for a while.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 20, 2017 at 2:19 pm
Phil Parkin - Thursday, July 20, 2017 2:12 PMSQL-DBA-01 - Thursday, July 20, 2017 2:04 PM2377688866Based on this comment
Id column is already in BigInt (has been changed to fit more width from Int)
You have 9,223,372,036,854,775,807 - 2,377,688,866 = 9.2 * 10^18 identities remaining.
Should be OK for a while.
I agree with Phil on this one. There is no reason to reseed the column.
July 20, 2017 at 2:32 pm
SQL-DBA-01 - Thursday, July 20, 2017 1:47 PMI used the below approach. For testing I created another mimic table and tried. It is working. But for large table it will be a mess , I assume.
1. Create a temp table with the same structure
2. Import values from table
3. Remove constraints from tmp table
4. Drop column id from tmp table.
5. Add a new ID column with Identity(1,1) property
6. Drop main table
7. Rename the table to the intended table name.
8. Add back the constraint (Primary Key)
9. Check the results.
I agree with everyone else here, there isn't much need to reseed the column yet.
BUT in the event that you want to, I would change a few things with your above steps. I would:
1 - Create a temp table with the same structure but no identity column
2 - import values from main table into the temp table excluding the identity column
3 - truncate the main table
4 - import values from the temp table into the main table
Fewer steps and fewer places for things to go badly. Problems may come into play if you have foreign keys on there or some odd constraints, but based on your steps, I don't think this is the case. Another way you could do it if you don't want to move the data twice:
1 - create the temp table with the same structure including the identity column and including the PK constraint on the identity column
2 - import values from the main table into the temp table excluding the identity column (as it will auto-populate)
3 - rename the main table (so you have a backup or if you need to prove to end users that the only thing that changed was the identity column)
4 - rename the temp table
again, fewer steps with the same end result.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 20, 2017 at 2:41 pm
I'll pile on and also agree that in your situation it doesn't sound like there's a pressing need to eliminate gaps; it sounds very much like a solution in search of a problem 🙂
Another thought: if gaps in the identity values really bother you and you absolutely must eradicate them, and if the identity column you're using as your primary key is something the values of which you're willing and able to change weekly or monthly (indicating they're not referenced in a bunch of other tables), I'd reconsider having an identity column as primary key in the first place.
In such a case, it might well be worth looking for other candidate keys that make sense for the table and wouldn't tempt you to do something like this on a regular basis 🙂
Cheers!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply