Row level compression drastically reduces the meta-data needed for variable length columns. Instead of storing the length of each field in 2 bytes, it now takes a mere 3 bits. Fields themselves are also smaller. Storing a 1 in an int field now only takes a single byte, though of course larger values may use up to 4 bytes.
In my opinion the new feature shouldn’t really be called compression but rather removal of wasted space. Although the concept of sharing common data from multiple rows in the same page is a form of compression and I suppose where the true value lies. It should make for some really interesting indexing strategies and could even motivate some edge use cases for denormalized data.
I really like the built-in backup compression and the idea that pages will only be compressed when its nearly full. I think the decreased physical data storage I/O will have a significant performance benefit over the CPU performance penalty introduced by the compression and decompression of data.
[tags]SQL Server 2008[/tags]