Find Out if a Partition is Compressed in SQL Server (T-SQL)

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.

Continue reading

Compress a Specific Partition within a Table in SQL Server (T-SQL)

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.

Continue reading

Create a Table with Compression in SQL Server (T-SQL)

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.

Continue reading

Estimate Data Compression Savings in SQL Server

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.

Continue reading