What is ColumnStore Index in SQL Server?

First of all ColumnarStorage Index  is a new feature in SQL Server 2012 and upward versions. This new indexing type is designed to speed up disk I/O operations in queries retrieving data from data warehouses where it is typically used. In data warehousing with ColumnarStorage Index; data is stored in columnar fashion. Hence if the data consists of large number of columns but only few columns are regularly called upon in a query, then having those columns (indexed) arranged to store data in the sense of columnar storage will be more efficient.

Probably this Mickey Mouse picture shows these two different index types.

Microsoft puts it this way,

"An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index. This differs from traditional indexes which group and store data for each row and then join all the rows to complete the whole index.
"
There are many restrictions on creating Columnar Storage Index, the first of which is that you can only do it if you have SQL Server 2012 Enterprise edition.

These are the basic restrictions for the columnstoreindex:

• Cannot have more than 1024 columns.
• Cannot be clustered. Only nonclustered columnstore indexes are available.
• Cannot be a unique index.
• Cannot be created on a view or indexed view.
• Cannot include a sparse column.
• Cannot act as a primary key or a foreign key
• Cannot be changed using the ALTER INDEX statement. Drop and re-create the columnstore index instead. (You can use ALTER INDEX to disable and rebuild a columnstore index.)
• Cannot be created with the INCLUDE keyword.
• Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms. Sorting is not allowed in the index. Values selected from a columnstore index might be sorted by the search algorithm, but you must use the ORDER BY clause to guarantee sorting of a result set.
• Does not use or keep statistics in the manner of a traditional index.
• Cannot contain a column with a FILESTREAM attribute. Other columns in the table that are not used in the index can contain the FILESTREAM attribute.
• Also, when it comes to updating a table, you cannot update a table with a columnstore index.

More info here.