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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2