There are two types of compression in sql server.
1) Data Compression
2) Backup Compression
Data Compression directly affects on mdf files which has another two types.
1) Row-Level Compression
It converts fixed length data types to variable length data types, freeing up empty spaces, ignore null and zero values.
2) Page-Level Compression
Page-Level Compression has prefix and dictionary compression along with row-level compression.
Get the estimate of data compression
sp_estimate_data_compression_savings
@schema_name = 'sales'
, @object_name = 'salesorderdetail'
, @index_id = NULL
, @partition_number = Null
, @data_compression = 'row'
Passing null in @index_id parameter that means it covers all indexes that created on that table and you can also pass index no of them.
To get index id
select si.index_id from sys.indexes as si
join sys.tables st on st.object_id = si.object_id where ST.name = 'salesorderdetail'
Passing the null in @partition_number parameter takes all partions.
To Get partition number
select sp.partition_number from sys.partitions as sp
join sys.tables as st on st.object_id = sp.object_id
join sys.indexes as si on st.object_id = si.object_id
where ST.name = 'salesorderdetail'
--Row Compression.
USE [AdventureWorks2012]
ALTER TABLE [Sales].[salesorderdetail] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)
--Page Compression.
USE [AdventureWorks2012]
ALTER TABLE [Sales].[salesorderdetail] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = Page
)
--DeCompression.
USE [AdventureWorks2012]
ALTER TABLE [Sales].[salesorderdetail] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = None
)
2) Back up Compression
Backup compression occurs at the time of database backup. It’s used to compress the backup size. You must have to enable backup compression to get the advantage of compression backup. To do so, go to
Right click on your server instance à Go To database setting tab from the left panel à check compress backup.
We will learn how data compression affects on performance in next blog.