Unicode compression supports the fixed-length nchar(n) and nvarchar(n) data types. Data values that are stored off row or in nvarchar(max) columns are not compressed.

Note:
nvarchar(max) data is never compressed even if it is stored in row.

When a SQL Server database is upgraded to SQL Server 2008 R2, Unicode compression–related changes are not made to any database object, compressed or uncompressed. After the database is upgraded, objects are affected as follows:

• If the object is not compressed, no changes are made and the object continues to function as it did previously.
  
• Row- or page-compressed objects continue to function as they did previously. Uncompressed data remains in uncompressed form until its value is updated.
  
• New rows that are inserted into a row- or page-compressed table are compressed using Unicode compression.
  

  Note:
  To take full advantage of the benefits of Unicode compression, the object must be rebuilt with page or row compression.

When an index is created or rebuilt or when a value is changed in a table that was compressed with row or page compression, the affected index or value is stored compressed only if its compressed size is less than its current size. This prevents rows in a table or index from increasing in size because of Unicode compression.

The storage space that compression saves depends on the characteristics of the data that is being compressed and the locale of the data. The following table lists the space savings that can be achieved for several locales.

Concepts

Other Resources

Help and Information