Export (0) Print
Expand All
Expand Minimize

CREATE COLUMNSTORE INDEX (Transact-SQL)

Creates a nonclustered in-memory columnstore index on a SQL Server table. Use a non-clustered columnstore index to take advantage of columnstore compression to significantly improve query execution times on read-only data.

To create a clustered columnstore index, see CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).

For more information, see these topics:

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database Update (preview) (Get it).

Topic link icon Transact-SQL Syntax Conventions

Create a non-clustered columnstore index.
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON [database_name. [schema_name ] . | schema_name . ] table_name  
        ( column  [ ,...n ] )
    [ WITH ( <columnstore_index_option> [ ,...n ] ) ]
    [ ON {
        partition_scheme_name ( column_name ) 
        | filegroup_name 
        | "default" 
        } 
    ]
[ ; ]

<columnstore_index_option> ::=
{
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
 }

index_name

Specifies the name of the index. index_name must be unique within the table, but does not have to be unique within the database. Index names must follow the rules of identifiers.

For a non-clustered columnstore index,

( column [ ,...n ] )

Specifies the columns to store. A non-clustered columnstore index is limited to 1024 columns.

Each column must be of a supported data type for columnstore indexes. See Limitations and Restrictionsfor a list of the supported data types.

ON [database_name. [schema_name ] . | schema_name . ] table_name

Specifies the one-, two-, or three-part name of the table that will contain the index.

ON

These options specify the filegroups on which the index will be created.

partition_scheme_name ( column_name )

Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. The partition scheme must exist within the database by executing CREATE PARTITION SCHEME. column_name specifies the column against which a partitioned index will be partitioned. This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name is not restricted to the columns in the index definition. When partitioning a columnstore index, Database Engine adds the partitioning column as a column of the index, if it is not already specified.

If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.

A columnstore index on a partitioned table must be partition aligned.

For more information about partitioning indexes, see Partitioned Tables and Indexes.

filegroup_name

Specifies a filegroup name on which to create the index. If filegroup_name is not specified and the table is not partitioned, the index uses the same filegroup as the underlying table. The filegroup must already exist.

"default"

Creates the specified index on the default filegroup.

The term default, in this context, is not a keyword. It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default]. If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

DROP_EXISTING

Specifies that the named, preexisting index is dropped and rebuilt. The default is OFF.

ON

The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, or index options.

OFF

An error is displayed if the specified index name already exists. The index type cannot be changed by using DROP_EXISTING. In backward compatible syntax, WITH DROP_EXISTING is equivalent to WITH DROP_EXISTING = ON.

MAXDOP = max_degree_of_parallelism

Overrides the Configure the max degree of parallelism Server Configuration Option configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.

max_degree_of_parallelism values can be:

  • 1 - Suppress parallel plan generation.

  • >1 - Restrict the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload. For example, when MAXDOP = 4, the number of processors used will be 4 or less.

  • 0 (default) - Use the actual number of processors or fewer based on the current system workload.

For more information, see Configure Parallel Index Operations.

Note Note

Parallel index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2014.

Requires ALTER permission on the table.

A columnstore index can be created on a temporary table. When the table is dropped or the session ends, the index is also dropped.

If you have a column that has a data type that is not supported for columnstore indexes, you must omit that column from the columnstore index.

Nonclustered columnstore indexes:

  • Cannot have more than 1024 columns.

  • A table with a nonclustered columnstore index can have unique constraints, primary key constraints, or foreign key constraints, but the constraints cannot be included in the nonclustered columnstore index.

  • Cannot be created on a view or indexed view.

  • Cannot include a sparse column.

  • Cannot be changed by using the ALTER INDEX statement. To change the nonclustered index, you must drop and re-create the columnstore index instead. You can use ALTER INDEX to disable and rebuild a columnstore index.

  • Cannot be created by using the INCLUDE keyword.

  • Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms. Sorting would eliminate many of the performance benefits.

Each column in a columnstore index must be of one of the following common business data types.

  • datetimeoffset [ ( n ) ]

  • datetime2 [ ( n ) ]

  • datetime

  • smalldatetime

  • date

  • time [ ( n ) ]

  • float [ ( n ) ]

  • real [ ( n ) ]

  • decimal [ ( precision [ , scale ] ) ]

  • money

  • smallmoney

  • bigint

  • int

  • smallint

  • tinyint

  • bit

  • nvarchar [ ( n ) ] except nvarchar (max) is not supported

  • nchar [ ( n ) ]

  • varchar [ ( n ) ]

  • char [ ( n ) ]

  • varbinary [ ( n ) ] except varbinary (max) is not supported

  • binary [ ( n ) ]

Applies to: SQL Server (SQL Server 2014 through current version).

  • uniqueidentifier

Columns that use any of the following data types cannot be included in a columnstore index.

  • ntext, text, and image

  • varchar(max) and nvarchar(max)

  • rowversion (and timestamp)

  • sql_variant

  • CLR types (hierarchyid and spatial types)

  • xml

Applies to: SQL Server 2012.

  • uniqueidentifier

Columnstore indexes cannot be combined with the following features:

  • Page and row compression, and vardecimal storage format (A columnstore index is already compressed in a different format.)

  • Replication

  • Change tracking

  • Change data capture

  • Filestream

For information about the performance benefits and limitations of columnstore indexes, see Columnstore Indexes Described.

All of the columns in a columnstore index are stored in the metadata as included columns. The columnstore index does not have key columns. These system views provide information about columnstore indexes.

[ TOP ]

A. Create a simple nonclustered columnstore index

The following example creates a simple table and a clustered index, and then demonstrates the syntax of creating a nonclustered columnstore index.

CREATE TABLE SimpleTable
(ProductKey [int] NOT NULL, 
OrderDateKey [int] NOT NULL, 
DueDateKey [int] NOT NULL, 
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON SimpleTable (ProductKey);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey);
GO

B. Create a simple nonclustered index using all options

The following example demonstrates the syntax of creating a nonclustered columnstore index by using all options.

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING =  ON, 
    MAXDOP = 2)
ON "default"
GO

For a more complex example using partitioned tables, see Columnstore Indexes Described.

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

[ TOP ]

Community Additions

ADD
Show:
© 2014 Microsoft