When you upgrade your database to SQL Server 2008 R2 from earlier versions of SQL Server, the Unicode compression related changes are not implemented to any of the database’s (compressed or uncompressed) objects. When you add a new row to a table which is already Row or Page compressed, the Unicode compression feature automatically compresses NCHAR or NVARCHAR columns. At the same time Unicode compression is enabled on a table which is already Row or Page compressed when a value is updated for either of the NCHAR or NVARCHAR columns. As a best practice once you upgrade your SQL Server 2008 database which already has Row or Page level compression enabled to SQL Server 2008 R2, you should REBUILD objects with Row or Page compression to take full advantage of the Unicode Compression Feature of SQL Server 2008 R2.
Conclusion
A DBA needs to be very careful before enabling Data Compression for any table or index within a database. Data Compression is a CPU intensive operation and if your environment is already facing a CPU crunch then this is not the best solution to look for. Hence, I recommend a thorough investigation before enabling Data Compression Feature on any of the Production tables or indexes.
]]>