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 |
|
midi25
Starting Member
24 Posts |
 Posted - 2005-05-23 : 09:14:17
|
| Hi I seem to have some framentation in my identity columns. Eg: Customer table contains new and old customers to my site. If certain customers wish to be removed from my site they can delete their details. These identity seeds are then lost. What I want to know is how can I reuse them. Below is a quote from SQL Server Books Online. Note If an identifier column exists for a table with frequent deletions, gaps can occur between identity values; deleted identity values are not reused. To avoid such gaps, do not use the IDENTITY property. Instead, you can create a trigger that determines a new identifier value, based on existing values in the identifier column, as rows are inserted. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-05-23 : 09:24:53
|
| If you reword your statement "What I want to know is how can I reuse them." into the question "WHY I reuse want to them."....you may solve this problem better for yourself.WHY care about lost numbers....unless for the sake of neatness/being sequential.....If the identidy colum is big enough, you should not runout of numbers even with an excessive churn on your customer base....if the latter is happening to you, then you busienss has got far more serious problems to worry about than gaps in your sequence numbers.If you are determined....search here for other advice. |
 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-05-23 : 09:28:10
|
quote:
Hi I seem to have some framentation in my identity columns. Eg
Is this realy a problem?If so you could run a sp to find all missing values ad drop them into a temp table select the 1st and use it to insert the new record by 1st turning off the Identity property using(SET IDENTITY_INSERT)then inserting and turing it back off.note:I only use this when I need to reactivate a deleted account not to fill in spaces. JimUsers <> Logic |
|
|
|
mpetanovitch
Yak Posting Veteran
52 Posts |
Posted - 2005-05-23 : 12:17:51
|
| Another reason not to reuse:If you have your identity as a primary key and its clustered you should never reuse unless there is a very good reason. By reusing you are no longer inserting into the leaf pages in a sequential order. Sql server is smart enough to know that if you are inserting in sequential order on a clustered key that 50-50 page splits never occur on this key, it will just allocate another 8k page and start adding to the top of that. This is a huge benifit for highly transactional tables. Mike Petanovitch |
|
|
|
|
|
|
How can I re use deleted identity numbers ?