For an instructor lead, in-depth look at learning SQL click below.
As the volume of data that businesses manage continues to expand, the cost of storage also increases. To manage this growth and maintain efficiency, SQL server data compression becomes critical. This practice can help businesses significantly reduce storage costs while also improving the performance of data retrieval. In this blog post, we’ll be focusing on how to optimize your storage space using SQL Server. Firstly, let’s understand what data compression is:
Data compression is the practice of reducing the size of data stored without losing original information. SQL Server provides two primary data compression options: row compression and page compression.
Using ROW Compression
In SQL Server, row compression changes the format of physical storage of data, reducing the amount of storage required. Let’s look at an example:
|
1 2 3 4 5 6 |
--This statement uses row compression on the 'SalesOrderDetail' table in 'AdventureWorks2014' database ALTER TABLE Sales.SalesOrderDetail REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW); |
Using PAGE Compression
Page compression is a combination of row compression and two additional techniques: prefix compression and dictionary compression. Here’s how you could apply page compression to a table:
|
1 2 3 4 5 6 |
--This statement uses page compression on the 'SalesOrderDetail' table in 'AdventureWorks2014' database ALTER TABLE Sales.SalesOrderDetail REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); |
Page compression can bring substantial savings in storage space, although it requires more CPU overhead than row compression.
Estimating Compression Savings
Before you decide to compress a table or index, you might want to estimate the amount of disk space that compressing a specific table or index will save. You can do so using the stored procedure ‘sp_estimate_data_compression_savings’. Here is an example of how this function can be used:
|
1 2 3 4 5 6 7 8 9 10 |
--This statement returns the estimated savings from compressing 'SalesOrderDetail' table EXEC s<a href="mailto:p_estimate_data_compression_savings @schema_name" >p_estimate_data_compression_savings @schema_name</a> = 'Sales', @object_name = 'SalesOrderDetail', @index_id = 0, @partition_number = NULL, @data_compression = 'PAGE'; |
This stored procedure returns the current size of the table or index, and estimates the size if it’s compressed using the specified compression setting.
Conclusion
Strategically compressing your data in SQL Server can minimize storage costs and improve the performance of your database queries. It’s important to evaluate and decide between row and page compression options depending on the nature of your data and business requirements. Also, always estimate and determine whether compression will provide benefits for a specific table or index before applying it, to make the best use of resources.