New feature in SQL Server 2008 - compression how it works?
Since the release of SQL Server 2008 (last 2 CTPs) users are anxiously awaiting for data compression feature, so how it is handled in this version and what it does, is it a good thing?
Data compression enables you to store your data more effectively and reduce the storage requirements for your data. SQL Server 2008 provides significant performance improvements for large I/O bound workloads such as data warehousing. Not only the data compression even SQL development team are looking to input native support out-of-the box for backup compression, which is another most wanted feature in SQL Server.
The basic need of data compression is to get right balance between how data is compressed without compromising the performance cost, also while de-compressing the data. There are many third party tools out there provide such compression feature which is good in terms of size but when it comes to real-play they will fall behind zero in providing the performance results. Being the basic design of compression with the CPU cost of SELECT and DML operations become unacceptably high, in this regard I have had a chat with SQL Development team during SQLPASS event. So SQL Server development team evaluated various compression techniques, estimated the data compression we could achieve for different DW databases, estimated the CPU impact of compression/decompression and then zeroed-in to the types of compression to deliver what we have in SQL Server 2008. That means it is not so easy to accomplish the task, whereas it willbe provided within next CTP releases and not sure whether it will be shipped with RTM release or not. Based upon the community usage and feedback it will be very easy to fine tune the processes and input the same in upcoming Service Pack or Cumulative Update releases.
Here is a great explanation on how Compression works by Chad Byod - SQL2008-Compression blog post and Aaron's Elaborations.
Hope this gives basic idea about data compression and backup compression that will be part of upcoming SQL Server 2008.