Denali — Day 5: Column-store indexes (aka Project Apollo)

Denali – Day 5: Column-store indexes (aka Project Apollo)


There is another great achievement for Denali, especially for Data warehouse, where data is static and very huge – READONLY, and user always require data to be retrieved quickly as possible. So in Denali another great feature called “Column stored” index. the way data is stored in a page is changed. Generally each page stores (row)record of a table. But when you create a “column stored” index page contains columns. This technology is build on Power Pivot Analytic services for sql server 2008 and on this basis VeriPaq compression is used.

When you query table you requires data for particular column but as pages stores rows it has to go through all the rows and if it’s large data much rows to be scanned and pages as well. But when you stored that in columnstored indexed key. You will access fewer pages to be scan and specific column data would be retrieved .. and will be quite fast.


Experts confirmed that for very large Read-only data warehouse system “Column stored” is provided improvement of 10 to 100 times.

This is the good about Column stored, it has its own limitations as well.

  1. Only one columnstored index allowed per table
  2. Only non-clustered columnstored index is allowed
  3. Table contains columnstored index could not be part of Replication, CDC, Index view.
  4. Columnstored index cannot be created on columns of FileStream, computed, sparse, BLOB


Different ways to use Columnstored index for DML operations are

  1. Disable Columnstored index and rebuild
  2. Partition
  3. Union ALL – not a good idea but an option…


Use T-SQL as



    ON <object> ( column [ ,…n ] )

    [ WITH ( <column_index_option> [ ,…n ] ) ]

    [ ON {

           { partition_scheme_name ( column_name ) }

           | filegroup_name

           | “default”



[ ; ]




Only 1024 columns can be added into this.

*Again use this only for large data with READONLY. Not good for OLAP DML small/medium object.

This entry was posted in Denali, Index, Performance Tuning and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.