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