Individual Partition Compression
Published: 5th May 2015
To continue with partitioning there’s another good feature to consider which involves combining it with another Enterprise feature… compression.

What partitioning can offer is the ability to offer compression at a partition level and therefore allow you to get the best from your SQL Server from all angles in regard to disk space and performance.

This is very easy to implement and very powerful… how many times have you had a very large table which holds data going back several years and although you have partitioning implemented to make queries relatively fast (via partitioning), the table itself is starting to get large and you can’t do anything about it?

Well one option is to compress your older data, taking up less space on disk, and only incurring overhead when that specific data is read, but not having any impact on any other data within your table, rather than having to incur the compression read penalty on your table as a whole.

Let’s have a quick demo as to how to implement this… I’ll avoid doing analysis on the compression ratios etc as this uses the standard page or row compression and therefore that’s covered elsewhere on my blog if you want to see it…

I don’t want to post a lot of unnecessary code here and therefore I’m going to insist that you look at my previous post HERE… but once you have the table set up and you know which is the oldest partition (which you want to compress and is actually partition 1 in my code) then you’ll be ready to actually compress that single partition…

alter table testPartition rebuild partition = 1 with (data_compression = page)

Just a quick check to prove it’s just the one partition and the remainder is as normal:

select partition_number, data_compression, data_compression_desc
from sys.partitions
where object_id = object_id('testPartition')
order by partition_number

NB: Comments will only appear once they have been moderated.