Using Nonclustered Columnstore Indexes

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.

Contents

• Create a Nonclustered Columnstore Index

• Change the Data in a Nonclustered Columnstore Index

Create a Nonclustered Columnstore Index

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.

Change the Data in a Nonclustered 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 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
  

• Drop the columnstore index, update the table, and then re-create the columnstore index with CREATE COLUMNSTORE INDEX. For example:

  DROP INDEX mycolumnstoreindex ON mytable
  -- update mytable --
  CREATE NONCLUSTERED COLUMNSTORE INDEX mycolumnstoreindex ON mytable;
  
  

• 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.