I have a confusion on how this compression feature works in SQL server:
For quite some large tables , ones with over TB, we recently implemented PAGE level compression.
Example of how it was done:-
Use [DBname] ALTER TABLE dbo.sampletable REBUILD PARTITION =1 WITH (DATA_COMPRESSION = PAGE); ALTER TABLE dbo.sampletable REBUILD PARTITION =2 WITH (DATA_COMPRESSION = PAGE);
and so on...
The tables in question were all partitioned table.
When checking the details of compression i see at table level ( Clustered index ones) it shows compression =
PAGE but for existing non clustered indexes , compression says =
I am not sure what should i interpret from above if compression is enabled at table level or needs to be enabled at index level as well?
Is there space and performance benefit in case we should also consider compressing NC indexes as well? Basically what would be my ideal case here to go with compressing them as well?