Using Nonclustered Columnstore Indexes
Collapse the table of content
Expand the table of content

Using Nonclustered Columnstore Indexes

SQL Server 2014

Describes key tasks for using a nonclustered columnstore index on a SQL Server table.

For an overview of columnstore indexes, see Columnstore Indexes Described.

For information about clustered columnstore indexes, see Using Clustered Columnstore Indexes.

To load data into a nonclustered columnstore index, first load data into a traditional rowstore table stored as a heap or clustered index, and then use CREATE COLUMNSTORE INDEX (Transact-SQL) to create a columnstore index.

Loading data into a columnstore index

As the original table is updated, the nonclustered columnstore index will not be updated automatically and will become out-of-date. To update the nonclustered columnstore index, you can either use ALTER INDEX with the REBUILD option to re-create the columnstore index, or you can drop the index and re-create it with CREATE CLUSTERED COLUMNSTORE INDEX.

Once you create a nonclustered columnstore index on a table, you cannot directly modify the data in that table. A query with INSERT, UPDATE, DELETE, or MERGE will fail and return an error message. To add or modify the data in the table, you can do one of the following:

  • Disable or drop the columnstore index. You can then update the data in the table. If you disable the columnstore index, you can rebuild the columnstore index when you finish updating the data. For example,

    ALTER INDEX mycolumnstoreindex ON mytable DISABLE;
    -- update mytable --
    ALTER INDEX mycolumnstoreindex on mytable REBUILD
  • Load data into a staging table that does not have a columnstore index. Build a columnstore index on the staging table. Switch the staging table into an empty partition of the main table.

  • Switch a partition from the table with the columnstore index into an empty staging table. If there is a columnstore index on the staging table, disable the columnstore index. Perform any updates. Build (or rebuild) the columnstore index. Switch the staging table back into the (now empty) partition of the main table.

© 2016 Microsoft