Yep, 'test it and see' indeed.
If you have lots of half-empty Char(100) columns, I guess row-compression may speed things up even comparing memory access speeds.
My tests were using an efficient design where 'every byte counts', so compression would have a harder time to prove its worth.
Once we move the transactional system to 2008, I'll test that one for compression gains (it will still fit in memory uncompressed, but the schema is inefficient so maybe compression will be worth it).
SQL Server matters are rarely cut'n'dry eh...
Cheers,
JohnA
MCM: SQL2008