Unicode Compression Example
Let us go through an example which demonstrates the implementation of Unicode Compression on Person.Address table of the AdventureWorks2008 sample database.
In this example, we will first see the estimated space saving which can be achieved when Row and Page level compression is enabled on Person.Address table of AdventureWorks2008 database, when the database resides on SQL Server 2008.
Second step will be to restore AdventureWorks2008 database on to SQL Server 2008 R2 and then enable Row and Page level compression on Person.Address table of AdventureWorks2008 database to see how much extra space can be saved when Unicode Compression feature of SQL Server 2008 R2 comes into effect automatically.
The Person.Address table of AdventureWorks2008 database has four columns which uses NVARCHAR data type. The columns which use NVARCHAR data type are AddressLine1, AddressLine2, City and PostalCode. In SQL Server 2008 since Unicode Compression feature was not available these columns could not be compressed. However, in SQL Server 2008 R2 Unicode compression will compress these columns thereby saving more disk space. The above mentioned Unicode columns will automatically get compressed once you enable Row or Page level compression.
The structure of Person.Address table of AdventureWorks2008 database is shown in the screenshot below.
Estimated Space Saving when Row and Page Level Compression is enabled in SQL Server 2008
In the below screenshot you could see that when you enable Row level compression on the Person.Address table of the AdventureWorks2008 database in SQL Server 2008 the requested compressed space is 4.930 MB.
Similarly, when you enable Page level compression on the Person.Address table of AdventureWorks2008 database in SQL Server 2008 the requested compressed space is 4.367 MB.
Estimated Space Saving when Row and Page Level Compression is enabled in SQL Server 2008 R2
In the below snippet you could see that when you enable Row Level compression on the Person.Address table of the AdventureWorks2008 database in SQL Server 2008 R2 the requested compressed space is 3.688 MB which is far less than 4.930 MB, when Row Level compression was enabled in SQL Server 2008 for Person.Address table.
Similarly, when you enable Page Level compression on the Person.Address table of the AdventureWorks2008 database in SQL Server 2008 R2 the requested compressed space is 3.281 MB which is far less than 4.367 MB, when Page Level compression was enabled in SQL Server 2008 for the Person.Address table.
