Row-Level Compression

Andy Mallon explains row-level compression:

You can think of row compression as working by treating certain fixed-length data types as variable-length data types. By removing certain metadata, NULL and 0 values, and the padding of fixed-length values, SQL Server can reduce the total size of a row.

The easiest way to think of it is that char(n) no longer takes n bytes for every row, but instead gets treated more like varchar(n) where the storage used varies for each value. The behavior for each data type varies, with some data types getting more or less (or no) savings compared to others.

Row-level compression is the “safer” of the two primary compression options, but I almost never use it.  That might just be a function of the my particular workloads, of course.

Related Posts

Backup Compression And Encryption

Arun Sirpal shows the combined effects of backup encryption and backup compression in SQL Server 2017: Do not forget about the certificate! Warning: The certificate used for encrypting the database encryption key has not been backed up. Imagine if you need to recover the backup and you can’t?  You will get the dreaded thumbprint error. […]

Read More

Finding Compressable Indexes

Tracy Boggiano has a script to help you figure out which indexes make sense to compress: We can write procedure check periodically rather a table will benefit from compression or not.  There are a few tricks though: If you have sparse columns you cannot compress the table, we check for that in lines 70-74. Next […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29