Creating column store index on few columns

Question

[SQL Server 2012]

I have a table with 10 columns. Other than a clustered index, I have non-clustered indexes on five columns. But I want the results from only three columns and so I create a column-store index.

Is it possible to select few columns for column store index from a list of columns that are already indexed?

How can I use Column Store Index in OLTP environment? Is it specifically designed for OLAP?

Answer 1

Definitely not for OLTP. It makes the table read only so has to be rebuilt for every modification manually.  Long OLTP day that.

You probably could create the column store index on just a few columns, but the idea behind the columnstore index (in its current iteration for sure) is for read only analytics and typical use case will be do do all columns.

For more: http://msdn.microsoft.com/en-us/library/gg492088.aspx

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.