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.

Example

Here’s an example to demonstrate.

SELECT
    [partition_number],
    [data_compression],
    [data_compression_desc]
FROM sys.partitions
WHERE object_id = OBJECT_ID('Movies');

Result:

+--------------------+--------------------+-------------------------+
| partition_number   | data_compression   | data_compression_desc   |
|--------------------+--------------------+-------------------------|
| 1                  | 0                  | NONE                    |
| 2                  | 1                  | ROW                     |
| 3                  | 1                  | ROW                     |
| 4                  | 0                  | NONE                    |
+--------------------+--------------------+-------------------------+

In this case, partitions 2 and 3 use row compression. Partitions 1 and 4 are not compressed.