Column Store Index in Sql Server 2012

The data in dataware house keep growing day by day , data  processing in data mart sometimes becomes unmanageable. The larger point is performance and to keep things at its bench mark we need proactive setup therefore Column Store Index is one of the important facility we can use in table to speed of query performance.

http://www.mssqltips.com/sqlservertip/2586/sql-server-2012-column-store-index-example/

Courtesy above blog post

 

Limitations of SQL Server ColumnStore Indexes

There are several limitations of using SQL Server ColumnStore indexes over Row Store indexes including:

• A table with a ColumnStore Index cannot be updated
• ColumnStore index creation takes more time (1.5 times almost) than creating a B-tree index (on same set of columns) because the data is compressed
• A table can have only one ColumnStore Index and hence you should consider including all columns or at least all those frequently used columns of the table in the index
• A ColumnStore Index can only be non cluster and non unique index; you cannot specify ASC/DESC or INCLUDE clauses
• Not all data types (binary, varbinary, image, text, ntext, varchar(max), nvarchar(max), etc.) are supported
• The definition of a ColumnStore Index cannot be changed with the ALTER INDEX command, you need to drop and create the index or disable it then rebuild it
• You can create a ColumnStore index on a table which has compression enabled, but you cannot specify the compression setting for the column store index
• A ColumnStore Index cannot be created on view
• A ColumnStore Index cannot be created on table which uses features like Replication, Change Tracking, Change Data Capture and Filestream

• CREATE NONCLUSTERED COLUMNSTORE INDEX  
  ON 
  (
   Col1,
   Col2,
   ....
   ....
   Coln
  )
  GO