Compression without Compression in SQL server 2005 sp2 fact tables

  • Article

In case you weren't at the excellent SQL Community yesterday evening, I thought I would share a top tip for getting on top of your fact tables in the next version of SQL Server.  Mark Hill of Edenbrook gave the evening an update of the relational engine as it affect BI and we then got into a debate about the merits of the new compression feature.  My understanding is that is particularly effective against fact tables and can improve performance by reducing I/O albeit at the expense of CPU usage.  So Sanjay Nayyar from IM Group pointed out that you would get even greater benefit from use of the Vardecimal storage format as there will be many numbers in a typical fact table that will not use the designated number of bytes and this will have no CPU impact.  This feature came out as part of SQL server sp2 and of course Sanjay's absolutely right. doh!

So as ever there are lots of choices and you need to consider what works for a particular problem.  if you haven't got time to do that then get along to the SQL community as someone there will know the score and you can have a beer and pizza while you learn.

Thanks again for a really successful evening to:

  • MC and MVP Chris Webb on organisation
  • Mark Hill for his hard work on explaining MERGE and Change Data Capture  
  • IM Group for use of their offices and a quality introduction on Data Mining from Suranjan Som, and I hope he gets 5 minutes to post about it that would be great!

Technorati tags: SQL Server, SQL