CREATE COLUMNSTORE INDEX (Transact-SQL)

  • Article

Creates a columnstore index on a specified table. An xVelocity memory optimized columnstore index, is a type of compressed non-clustered index. There is a limit of one columnstore index per table. An index can be created before there is data in the table. A table with a columnstore index cannot be updated. For information about using columnstore indexes, see Columnstore Indexes.

Note

For information about how to create a relational index, see CREATE INDEX (Transact-SQL). For information about how to create an XML index, see CREATE XML INDEX (Transact-SQL). For information about how to create a spatial index, see CREATE SPATIAL INDEX (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

Syntax

CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON <object> ( column  [ ,...n ] )
    [ WITH ( <column_index_option> [ ,...n ] ) ]
    [ ON {
           { partition_scheme_name ( column_name ) } 
           | filegroup_name 
           | "default" 
         }
    ]
[ ; ]

<object> ::=
{
    [database_name. [schema_name ] . | schema_name . ]
     table_name
{

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

Arguments

  • NONCLUSTERED
    Creates a columnstore index that specifies the logical ordering of a table. Clustered columnstore indexes are not supported.

  • COLUMNSTORE
    Indicates the index will be a columnstore index.

  • index_name
    Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

  • column
    Is the column or columns on which the index is based. A columnstore index is limited to 1024 columns.

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

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

  • ON filegroup_name
    Creates the specified index on the specified filegroup. If no location is specified and the table or view is not partitioned, the index uses the same filegroup as the underlying table or view. The filegroup must already exist.

  • ON "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).

<object>::=

Is the fully qualified or nonfully qualified object to be indexed.

  • database_name
    Is the name of the database.

  • schema_name
    Is the name of the schema to which the table belongs.

  • table_name
    Is the name of the table to be indexed.

<column_index_option>::=

Specifies the options to use when you create the column store index.

  • 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 can be:

    • 1
      Suppresses parallel plan generation.

    • >1
      Restricts the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload.

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

    For more information, see Configure Parallel Index Operations.

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

Remarks

Indexes can be created on a temporary table. When the table is dropped or the session ends, the indexes are dropped.

The common business data types can be included in a columnstore index. The following data types can be included in a columnstore index.

  • char and varchar

  • nchar and nvarchar (except varchar(max) and nvarchar(max))

  • decimal (and numeric) (Except with precision greater than 18 digits.)

  • int, bigint, smallint, and tinyint

  • float (and real)

  • bit

  • money and smallmoney

  • All date and time data types (except datetimeoffset with scale greater than 2)

The following data types cannot be included in a columnstore index.

  • binary and varbinary

  • ntext, text, and image

  • varchar(max) and nvarchar(max)

  • uniqueidentifier

  • rowversion (and timestamp)

  • sql_variant

  • decimal (and numeric) with precision greater than 18 digits

  • datetimeoffset with scale greater than 2

  • CLR types (hierarchyid and spatial types)

  • xml

Basic Restrictions

A columnstore index:

  • Cannot have more than 1024 columns.

  • Cannot be clustered. Only nonclustered columnstore indexes are available.

  • Cannot be a unique index.

  • Cannot be created on a view or indexed view.

  • Cannot include a sparse column.

  • Cannot act as a primary key or a foreign key.

  • Cannot be changed using the ALTER INDEX statement. Drop and re-create the columnstore index instead. (You can use ALTER INDEX to disable and rebuild a columnstore index.)

  • Cannot be created by with 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.

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.

Permissions

Requires ALTER permission on the table.

Examples

A. Creating a simple nonclustered index

The following example creates a simple table and clustered index, and then demonstrates the syntax of creating a 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. Creating a simple nonclustered index using all options

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

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.

See Also

Reference

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_segments (Transact-SQL)

ALTER INDEX (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

Concepts

Columnstore Indexes

Columnstore Indexes