There are several compression types available;
- Row Compression
- Page Compression
- Unicode Compression
In SQL server we can run sp_estimate_data_compression_savings stored procedure to estimate amount of savings which we can have from each compression type.
In here I have used AdventureWorks2012 database for the demonstration. According to results, page compression will save lots of space. However, we must have a proper plan before compress data. Otherwise lots of issues may introduced.