In SQL Server, you can query the sys.partitions system catalog view to find out whether or not a partition has been compressed.
In particular, the data_compression column tells you whether it’s compressed or not. The data_compression_desc column tells you what type of compression it uses. If it isn’t compressed, it returns NONE.
When you compress a partitioned table in SQL Server, you can compress all partitions, some, or just one partition.
To do this, use the REBUILD PARTITION syntax within the ALTER TABLE statement.
When doing this, you can rebuild just the specified partition or all partitions. Alternatively, you can rebuild all partitions, while compressing only a specific partition or list of partitions.
When you create a table in SQL Server, you have the option of using data compression.
Data compression helps reduce the size of the database. It can also help improve performance of I/O intensive workloads due to the data being stored in fewer pages, therefore reducing the number of pages that queries need to read from disk.
To do this, use the DATA_COMPRESSION option when creating the table.
You can use the ALTER TABLE statement to enable compression on an existing table in SQL Server.
To do this, you need to use the REBUILD WITH option, while specifying your desired compression type.
SQL Server has a system stored procedure called sp_estimate_data_compression_savings, which allows you to check an object’s size and its estimated size with various levels of compression.
If the object is already compressed, you can use this procedure to estimate its size when recompressed.
Objects can be compressed by using row, page, columnstore or columnstore archive compression.
Compression can be evaluated for whole tables or parts of tables. This includes heaps, clustered indexes, nonclustered indexes, columnstore indexes, indexed views, and table and index partitions.
