>>>>>Hi everybody,
>>>>>
>>>>>In my VFP application I have a routine that deletes a bunch of records in a table and then has to create a bunch of records. To minimize the number of records marked as deleted I recycle records. Basically instead of deleting records I mark them in a field REC_STAT as "AVAILABLE" and when I need to add records I first check if "AVAILABLE" records exist and overwrite values there. This routine is kind of pain to maintain but I have learned to live with it.
>>>>>
>>>>>Now I am converting this routine to SQL Server. Is it going to be a problem for SQL Server if I don't recycle deleted records. That is, if I simply delete when I need to delete and create new when I need to create new. I don't know if SQL Server automatically packs tables or it has to be done in a separate routine as done in VFP. To give you a ball park number of records/rows in this table is as following:
>>>>>Maximum number of records 500,000
>>>>>Percent deleted would be about 10%.
>>>>>The above are real guess number and considerably larger than my typical customer database. But I wanted to know the worst impact of this situation.
>>>>>
>>>>>Thank you in advance for any input.
>>>>
>>>>When you delete record in SQL Server it is deleted. There is now way to get it back. No need to PACK (and there is no such command in SQL Server).
>>>>SQL Server maintains it own recycle routine and uses the place from already deleted records when you add new ones.
>>>>It is up to you if you want to get rid of MARK records and then re-use them or leave all to SQL Server.
>>>
>>>From what you are telling me, I can get rid of my "recycle" routine and delegate recycling to SQL Server. This will make my code better.
>>
>>Not entirely so. Users make mistakes. The reason Fox has a deleted flag is so that users could delete records and they could be recalled. What I do in VFP and SQL is datetime stamp my records when the user "deletes". Then the records could be recalled. I delete the records through a separate process. In VFP I also do a pack. This way "deleted" records can be kept around as long as the business requirements dictate.
>>
>>IOW the users never directly delete - the system does that. The user only gets to say they want these records to be deleted ... eventually.
>>
>>That means the app does not retrieve records marked for eventual deletion.
>>
>>HTH
>
>What I was describing as deleting and "recycling" is not what user does. The deleting is done by the application in order to perform a certain task. Therefore, these "deleted" records are not what a user would ever want to have back or even know of their existence.
>
>Thank you for your input.

Hi Dmitry

Like Mike, we set a flag on the row when the user "deletes" a record. We have a kind of OLTP system with many inserts and deletes during the day. We found that if we deleted the records when the user deleted, we were seeing a lot of blocking (and deadlocks). We defer the actual removal of records from tables to a batch job that runs nightly. Performance improved considerably.

Bruce