For an instructor lead, in-depth look at learning SQL click below.
An integral aspect of modern-day digital business is managing the large amounts of data that systems generate daily. For data administrators, optimizing storage and improving system performance without a significant cost burden is often a central priority. One of the ways to achieve this is through SQL Server Data Compression. This post will introduce SQL Server Data compression, discuss its merits, and then break down a few SQL scripts to show data compression in action.
What is SQL Server Data Compression?
SQL Server Data Compression is a feature that was introduced in SQL Server 2008 to minimize the amount of data storage required to store SQL data. There are two types of data compression in SQL server: Row-Level Compression and Page-Level Compression. This process decreases the amount of space necessitated to store data, and also increases the speed of data retrieval, thereby upgrading your system’s performance.
Row-Level Compression
|
1 2 3 4 5 |
-- The following script will implement row-level compression on a table ALTER TABLE TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW); |
The above SQL script compresses your table at the row level, effectively conserving storage space.
Page-Level Compression
|
1 2 3 4 5 |
-- The following script will implement page-level compression on a table ALTER TABLE TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); |
The preceding script demonstrates how to implement page-level compression on a SQL Server table. While consuming more CPU resources and taking longer to compress, page-level compression can offer more significant storage reduction than row-level compression.
Estimating Compression Savings
You can also estimate your savings before implementing any compression
|
1 2 3 4 5 6 7 8 9 10 |
-- The following script will help estimate the savings from compressing a table EXEC s<a href="mailto:p_estimate_data_compression_savings @schema_name" >p_estimate_data_compression_savings @schema_name</a> = 'SchemaName', @object_name = 'TableName', @index_id = NULL, @partition_number = NULL, @data_compression = 'PAGE'; |
The above SQL script provides estimates of the storage savings that can be achieved by compressing your SQL Server table at the page level.
Conclusion
In conclusion, managing large datasets in SQL Server can be made more efficient by employing data compression techniques. Not only will they help save valuable storage space, but they can also lead to improved query performance. Before you implement compression, be sure to use the provided scripts to preview the potential savings and identify what kind of compression works best for your needs.