Will I need to fix fragmentation after shrinking a database full of only HEAPs?
I have a 1 TB database that contains hundreds of quarterly archive tables and I am looking to drop any table older than 5 financial quarters, which will free up ~600gb of space. To provide an example of what the table architecture looks like:
myTable myTable_Q4_21 myTable_Q3_21 myTable2_Q4_21 myTable2_Q3_21
I am wondering if after dropping these tables and shrinking the database to reclaim the newly freed up space if I will need to either ALTER..REBUILD each remaining HEAP table or put a CX on each table and remove them afterwards.
Will you need to? No. But consider replacing them with clustered columnstore tables with archive compression. They will be defragmented, smaller, and much faster to scan.