Wednesday, September 7, 2011

SQL Server Denali: ColumnStore() a new Index Function

It is special type of index which stored columns in separate set of data pages instead of rows stored in data pages.It is optimized for the improved and fast warehouse queries processing. In ordinary index rows are stored in disk pages but with columnstore index columns are stored in separate set of the disk pages, that why it is more faster then ordinary index. It is mostly optimized for queries which are used aggregation , warehouse processing and BI application. 

CREATE NONCLUSTERED COLUMNSTORE INDEX mycolumnstoreindex ON mytable (col1, col2, col3);

You can use the Object Explorer in Management Studio to create the index as follows:
Expand the tree structure for the table and then right click on the Indexes icon.
Select New Index and then Nonclustered columnstore index
Click Add in the wizard and it will give you a list of columns with check boxes.
You can either choose columns individually or click the box next to Name at the top, which will put checks next to all the columns. Click OK.
Click OK.

No comments:

Post a Comment