Unicode Compression in SQL Server 2008 R2


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.


Continues…

Pages: 1 2 3




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |