Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Server 2012 Property Editors › Column Store Index › Define a Table ColumnStore Index in SQL Server 2012

Define a Table ColumnStore Index in SQL Server 2012

Use the SQL Server Table ColumnStore Index Editor to define table columnstore indexes in a SQL Server 2012 physical model.

Follow these steps:

1. In Model Explorer, expand Tables, right-click ColumnStore Indexes and click New.

   A ColumnStore Index is created.

2. Right-click the ColumnStore Index and click Properties.

   The SQL Server Table ColumnStore Index Editor opens.

3. Select the table from the Table drop-down that contains the index that you want to define.
4. (Optional) Edit the name of the ColumnStore Index.
5. Click the Members tab and work with the following options:

   Index Members

   Lists the columns that are defined to the table and lets you specify the columns to place into the index. You can use the toolbar to specify sort order, move columns up or down in the list, open the Column Editor to manage column properties, or open the Reset Order dialog to reset column order.

6. Click the Options tab to define Index Options and Filegroups.

   Clustered

   Creates columnstore index that specifies the logical ordering of a table. SQL Server 2012 does not support clustered columnstored index.

   Default: False

   Drop Existing

   Specifies that the named, preexisting index is dropped and rebuilt. Select the checkbox to drop the preexisting index and rebuilt it.

   Maximum Degree of Parallelism

   Limits the number of processors used in a parallel plan execution. The maximum number of processors that can be used is 64.

   Filegroup

   Creates a specified index on the specified filegroup.

7. Click the Partitions tab to define partition columns.
8. (Optional) Click the Comment tab and enter any comments that you want to associate with the Columnstore index.
9. (Optional) Click the Where Used tab to view where the Columnstore index is used within the model.
10. (Optional) Click the UDP tab to work with user-defined properties for the Columnstoe index.
11. (Optional) Click the Notes tab to view history information and view or edit user notes.
12. Click Close.

    The ColumnStore Index is defined and the SQL Server Table ColumnStore Index Editor closes.