Hey, Cloud Gurus.
Welcome to our lesson on compressing data.
In this lesson, we'll be taking a look
at our compression options.
And then we'll talk about compression of rowstore objects
and compression of columnstore objects
and wrap everything up with a review.
So let's start by looking at our compression options.
We have different options for rowstore objects
or columnstore objects.
For rowstore, we have row compression.
And this is the most basic level,
where it makes easy compression decisions
such as removing unused space and things like that.
From there, we have page compression,
which also includes row compression. But past that,
it looks for patterns in the data
and makes replacements with smaller values.
This is less useful if you don't have
lots of duplicate data,
because there won't be those repeated patterns
for it to look for and replace.
Under Columnstore, by default, all columnstore objects
have columnstore compression.
This is always on from the get-go and is not configurable.
But if you still want to compress it even further,
there is columnstore archival compression.
No matter which type of object you have
or which level of compression you choose,
one of the great benefits
of Azure SQL Database in the cloud
is that this is all rolled in.
In the past, you may have elected not to implement it
because the cost was so prohibitive
to have enterprise licenses,
but with Azure SQL Database, all of this goodness
is available in any of the pricing tiers.
Looking further into the compression of rowstore objects,
we do this for storage savings.
Of course, it reduces the size of the database
to compress these down.
And also, it improves I/O performance.
The data is stored on fewer pages,
and so there are fewer pages to retrieve
when you're carrying out operations.
The types of objects that this can be used on are
heap tables; clustered indexes,
which is the same as saying a table
that has a clustered index,
because it is stored as a clustered index;
non-clustered indexes; and indexed views.
For partitioned indexes and tables,
you can kind of mix and match
using a different compression type on each partition.
The different partitions do not have to have
the same compression setting.
Some considerations for our rowstore object compression:
it is available in Azure SQL Database, as I said,
but not every edition of SQL server.
Before you go to implement it,
check and make sure that the version you're using
has this capability.
Maximum row size plus compression overhead
can not exceed 8,060 bytes.
So keep that limitation in mind.
Row or page compression can be enabled or disabled,
both online or offline,
so it's nice to have that option
to keep on moving with your production workload
while enabling this compression.
Disk space requirements when enabling or disabling
are the same as when you're creating or rebuilding an index.
Here's an example of enabling compression.
We use the ALTER TABLE statement to carry this out,
telling it whether to rebuild
all of the partitions or specific ones.
And then within the width clause,
give it the type of compression to enable.
In this case, row compression.
Moving on to our columnstore objects,
as we said, columnstore compression is on by default
and it is not user configurable.
However, if you'd like to compress further,
there is columnstore archival compression,
and you use this when you need
to reduce the size of the data
and can afford the extra CPU and time overhead
required when retrieving the data.
The type of objects this can be used on
are a columnstore table or clustered index.
And that's really saying the same thing,
a columnstore table stored as a clustered index
is the same thing.
You can also use it on non-clustered columnstore indexes.
And just like with our rowstore objects,
this can be configured per partition.
Just because you enable
archival compression on 1 partition
doesn't mean you have to on the others,
and vice versa.
Some considerations with this type of compression,
indexes with columnstore archival compression
are going to be slower.
As we said, there is an extra CPU and time cost
when working with the data.
And so that is a sacrifice you make for the compression.
So we mostly use this to reduce the size of data
that is not accessed frequently.
That way you're not getting hit
with those extra costs very often.
Very similar to how we enabled compression
on our rowstore objects earlier,
we use the ALTER TABLE statement again,
specify which partitions to rebuild,
and then give our data compression
of columnstore_archive.
And it's as simple as that.
By way of review, for rowstore objects,
you can use row or Page Compression.
Columnstore objects have columnstore compression by default.
But if you want to kick that up a notch,
there is Columnstore archival compression
to further reduce the size of the data.
That's it for this lesson.
Thank you for joining me.
I hope it's been helpful for you.
Take some time to absorb that
and think about what compression type you might use
in your particular scenario.
And when you're ready,
I'll see you in the next video.