Hello,

In one of our SQL server, we are going to enable the data compression for all tables. i have one query in that, please clarify me.

my query is - when the compression is enabled for table, whether it is only for table or it includes all indexes in that table.

i searched in several forums, but i am not getting any clear answers. so i raised here.

Thanks

Balaji

Hi Blalaji,

If the compression is enabled for table, it is the same as set compression (either page or row) on the clustered index of a table. Because the REBUILD WITH syntax can be used to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index. REBUILD can be performed as an ONLINE operation.

Please note that the ALTER TABLE ... REBUILD syntax does not enable/disable compression of all nonclustered indexes on the table. It only affects the heap or the clustered index. So if we need to enable/disable compression of nonclustered indexes on the table, we should set the compression on the index separately.

The following similar threads are for your references:
http://dba.stackexchange.com/questions/49757/clustered-index-compression-vs-table-compression-are-they-the-same-thing
http://www.chilledsql.com/welcome/tip_category_compression/tip_detail_compression_compressalltablesandindexes

Thanks,
Katherine Xiong

Hi Xiong,

Thanks for the reply, its very helpful to me. i go ahead for compression now with this clear.

Regards

Balaji