CREATE COLUMNSTORE INDEX (Transact-SQL)

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

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.

Requires ALTER permission on the table.

A. Creating a simple nonclustered 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

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

Reference

Concepts