Side Effects of Compressing a Table or Index
When you compress a table or an index, you should be aware of two side effects:
- Compression includes a rebuild, thus removing fragmentation from the table or index.
- When a heap is compressed, if there are any nonclustered indexes on the heap, they are rebuilt as follows:
o With ONLINE set to OFF, the nonclustered indexes are rebuilt one by one.
o With ONLINE set to ON, all the nonclustered indexes are rebuilt simultaneously.
You must account for the workspace required to rebuild the nonclustered indexes, because the space for the uncompressed heap is not released until the rebuild of the nonclustered indexes is complete.
Vithal WadjePosted Aug 23, 2013, 2:46 PM