Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Individual Partition Compression
Published: Mar 05, 2017
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

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.