Hi David,
It still was an interesting read, wasn't? :) I wanted to reply immediately that the space would be re-used, but decided to double-check myself and started googling on the topic.
>Naomi,
>
>FWIW none of these referenced articles actually address Michel's question about data page reuse.
>
>
>Michel - yes SQL server will reuse space on the data and index pages as it sees fit. Over time though page fragmentation does happen to tables that have high insert activity, and definately on tables with high delete activity. So periodically you should drop all the indexes on a table, drop it's primary key constraint, then rebuild the primary key constraint and then rebuild the indexes.
>
>
>dbcc showcontig( 'TheTable' ) WITH tableresults, ALL_INDEXES
>
>
>will display the primary key/index fragmentation for a table. The most relevant columns are BestCount and ActualCount. If you see an index that's consuming twice as many pages as it could it's time for a rebuild of the index.
>
>SQL server has tools for routinely maintaining indexes but they don't do as good a job as a complete drop/recreate process does.
>
>Look at
>
>
>DBCC INDEXDEFRAG
>DBCC DBREINDEX
>
>>>If I add 1000 records in a table and remove all of them. Does SQL Server keep that empty space or will it recycle it? By that, I mean, once in a while, is there any reason to do a database compress?
>>
>>One more link on this topic
>>
>>
http://www.joehavelick.com/post/2008/02/27/Deleting-Records-and-Recovering-Space-in-SQL-Database.aspx
If it's not broken, fix it until it is.
My Blog