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

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

The Cost Of Compression

Erin Stellato looks at the COMPRESS function: While this data is interesting, I’m more curious about how compression affects my everyday SELECT queries. I have a set of three stored procedures that each have one SELECT query, so that each index is used. I created these procedures for each table, and then wrote a script […]

Read More

Categories

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