SQL Server Denali To Accelerate Performance with Columnar Storage

At PASS, Microsoft announced that SQL 11 (codenamed Denali) will support columnar storage. This announcement is incredible news for SQL developers wanting a simple solution to enabling rapid data analysis on their existing infrastructure. For some analytical workloads, columnar storage enables an order of magnitude faster performance.

Here is how it works (example from Wikipedia).

      1,Smith,Joe,40000;
      2,Jones,Mary,50000;
      3,Johnson,Cathy,44000;

A column-oriented database serializes all of the values of a column together, then the values of the next column, and so on.

      1,2,3;
      Smith,Jones,Johnson;
      Joe,Mary,Cathy;
      40000,50000,44000;

There are several advantages to this approach.

• Only data in your report needs to be loaded and processed
• Better compression
• OLAP-like indexing, pre-aggregation and cacheing
• More scalable data partitioning

Check out the Microsoft whitepaper here.