TABLE COMPRESSION
I was stuck when I had a large table in sql
server database (around 50 lacs records). and any operation with this table is
too slow. at that time when i did r&d on this topic I got an idea about table
compression. We can do compression only in Enterprise edition sql server. we can
do table compression with two types :
1. Row wise
2. Page wise
When i did page wise compression i have seen
before compression my table size was 120 mb (50lacs records).
after compression that table size was only 23
mb (50lacs records). Now my data operations going very good . before
compression when i deleted my 10 lacs records time taken around 30 minutes.
But now its going very smoothly.
I got some points about side effects of
compression. I am explaining below...
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.
Please do comments on above side effects...