In this article, we will delve into the concept of row compression in SQL Server. Row compression is a feature that optimizes the storage of zeros and null values in a database, resulting in significant space savings.

When using row compression, SQL Server does not allocate any disk space for zero or null values. This applies to all data types such as decimal, datetime, money, int, etc. If a row contains zero or null values for any of these data types, row compression ensures that they are not stored in the database at all.

To demonstrate the benefits of row compression, let’s consider the AdventureWorks database and the Production.WorkOrder table. Before applying compression, we can estimate the potential space savings using the stored procedure sp_estimate_data_compression_savings. This procedure provides information about the current compression, future compression after requested compression, current rows, and future reduced rows.

Once we have determined the potential compression savings, we can proceed with applying row compression to the table. This requires rebuilding the entire table with the Data_Compression = ROW clause enabled. The following command can be used to rebuild the table:

ALTER TABLE Production.WorkOrder REBUILD WITH (DATA_COMPRESSION = ROW)

After applying row compression, the size of the table is reduced by more than 1MB. This demonstrates the significant space savings that can be achieved with this feature.

It is important to note that there is also a concept of page compression in SQL Server, which is useful when there is a large amount of frequently occurring data. In a future article, we will explore page compression and analyze when it should be used.

In conclusion, row compression is a valuable feature introduced in SQL Server 2008. By optimizing the storage of zeros and null values, it allows for significant space savings in the database. I highly recommend trying row compression and taking advantage of this new data storage improvement feature.