Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
CREATE COLUMNSTORE INDEX (Transact-SQL)
Collapse the table of content
Expand the table of content

CREATE COLUMNSTORE INDEX (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Creates an in-memory columnstore index. Use a columnstore index to efficiently run real-time operational analytics on an OLTP workload or to improve data compression and query performance for data warehousing workloads.

To create a clustered columnstore index, use this statement to convert a rowstore table to a clustered columnstore index. Or, beginning with SQL Server 2016 Community Technology Preview 2 (CTP2), you can create the table as a clustered columnstore index.

Skip to examples:

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12 Premium Edition (Preview in some regions), SQL Server 2008 R2 Parallel Data Warehouse.

Topic link icon Transact-SQL Syntax Conventions

Convert a heap to a clustered columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [database_name. [schema_name ] . | schema_name . ] table_name
    [ WITH ( MAXDOP = max_degree_of_parallelism ) ]
    [ ON <on_option> ]
[ ; ]


Convert a clustered index to a clustered columnstore index.
Or, rebuild an existing clustered columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [database_name. [schema_name ] . | schema_name . ] table_name
    [ WITH ( < with_option> [ ,...n ] ) ]
    [ ON <on_option> ]
[ ; ]

Create a non-clustered columnstore index on a heap or clustered index rowstore table.
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON [database_name. [schema_name ] . | schema_name . ] table_name( column  [ ,...n ] )
    [ WITH ( < with_option> [ ,...n ] ) ]
    [ ON <on_option> ]
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
[ ; ]


<with_option> ::=
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] ) ]

<on_option>::=
      partition_scheme_name ( column_name ) 
    | filegroup_name 
    | "default" 

<filter_expression> ::=
      column_name IN ( constant [ ,...n ]
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant

CREATE CLUSTERED COLUMNSTORE INDEX

Create an in-memory clustered columnstore index in which all of the data is compressed and stored by column. The index includes all of the columns in the table, and stores the entire table. If the existing table is a heap or clustered index, the table will be converted to a clustered columnstore index. If the table is already stored as a clustered columnstore index, the existing index will be dropped and rebuilt.

index_name

Specifies the name for the new index.

If the table already has a clustered columnstore index, you can specify the same name as the existing index, or you can use the DROP EXISTING option to specify a new name.

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

Specifies the one-, two-, or three-part name of the table to be stored as a clustered columnstore index. If the table is a heap or clustered index the table will be converted from rowstore to a columnstore. If the table is already a columnstore, this statement will rebuild the clustered columnstore index.

WITH
DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON specifies to drop the existing clustered columnstore index, and create a new columnstore index.

The default, DROP_EXISTING = OFF expects the index name is the same as the existing name. An error will occur is the specified index name already exists.

MAXDOP = max_degree_of_parallelism

Overrides the existing maximum degree of parallelism server configuration 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 the max degree of parallelism Server Configuration Option, and Configure Parallel Index Operations.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

Specifies the data compression option for the specified table, partition number, or range of partitions. The options are as follows:

COLUMNSTORE

COLUMNSTORE is the default and specifies to compress with the most performant columnstore compression. This is the typical choice.

COLUMNSTORE_ARCHIVE

COLUMNSTORE_ARCHIVE will further compress the table or partition to a smaller size. Use this option for situations such as archival that require a smaller storage size and can afford more time for storage and retrieval.

For more information about compression, see Data Compression.

ON

With the ON options you can specify options for data storage, such as a partition scheme, a specific filegroup, or the default filegroup. If the ON option is not specified, the index will use the settings partition or filegroup settings of the existing table.

partition_scheme_name ( column_name )

Specifies the partition scheme for the table. The partition scheme must already exist in the database. To create the partition scheme, see 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.

filegroup_name

Specifies the filegroup for storing the clustered columnstore index. If no location is specified and the table is not partitioned, the index uses the same filegroup as the underlying table or view. The filegroup must already exist.

"default"

To create the index on the default filegoup, use "default" or [ default ].

If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. QUOTED_IDENTIFIER is ON by default. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

CREATE [NONCLUSTERED ] COLUMNSTORE INDEX

Create an in-memory nonclustered columnstore index on a rowstore table stored as a heap or clustered index. The index can have a filtered condition and does not need to include all of the columns of the underlying table. The columnstore index requires enough space to store a copy of the data. It is updateable and will be updated as the underlying table is changed. The nonclustered columnstore index on a clustered index enables real-time analytics.

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.

( column [ ,...n ] )

Specifies the columns to store. A nonclustered columnstore index is limited to 1024 columns.

Each column must be of a supported data type for columnstore indexes. See Limitations and Restrictions for 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.

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.

System_CAPS_noteNote

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

DATA_COMPRESSION

Specifies the data compression option for the specified table, partition number, or range of partitions. The options are as follows:

COLUMNSTORE

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE is the default and specifies to compress with the most performant columnstore compression. This is the typical choice.

COLUMNSTORE_ARCHIVE

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE_ARCHIVE will further compress the table or partition to a smaller size. This can be used for archival, or for other situations that require a smaller storage size and can afford more time for storage and retrieval.

For more information about compression, see Data Compression.

WHERE <filter_expression> [ AND <filter_expression> ]

Applies to: SQL Server (SQL Server 2016 Community Technology Preview 2 (CTP2) through current version).

Called a filter predicate, this specifies which rows to include in the index. SQL Server creates filtered statistics on the data rows in the filtered index.

The filter predicate uses simple comparison logic. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.

Here are some examples of filter predicates for the Production.BillOfMaterials table:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

For guidance on filtered indexes, see Create Filtered Indexes.

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

Requires ALTER permission on the table.

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

If the underlying table has a column of a data type that is not supported for columnstore indexes, you must omit that column from the nonclustered 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.

This example creates a table as a heap and then converts it to a clustered columnstore index named cci_Simple. This changes the storage for the entire table from rowstore to columnstore.

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

This example creates a table with clustered index, and then demonstrates the syntax of converting the clustered index to a clustered columnstore index. This changes the storage for the entire table from rowstore to columnstore.

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 CLUSTERED COLUMNSTORE INDEX cl_simple ON SimpleTable
WITH (DROP_EXISTING = ON);
GO

This example shows how to handle nonclustered indexes when converting a rowstore table to a columnstore index. Actually, beginning with SQL Server 2016 Community Technology Preview 2 (CTP2) no special action is required; SQL Server will automatically define and rebuild the nonclustered indexes on the new clustered columnstore index.

If you want to drop the nonclustered indexes, use the DROP INDEX statement prior to creating the columnstore index. The DROP EXISTING option only drops the clustered index that is being converted. It does not drop the nonclustered indexes.

In SQL Server 2012 and SQL Server 2014, you could not create a nonclustered index on a columnstore index. This example shows how in previous releases you need to drop the nonclustered indexes before creating the columnstore index.


--Create the table for use with this example.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL);
GO

--Create two nonclustered indexes for use with this example
CREATE INDEX nc1_simple ON SimpleTable (OrderDateKey);
CREATE INDEX nc2_simple ON SimpleTable (DueDateKey); 
GO

--SQL Server 2012 and SQL Server 2014: you need to drop the nonclustered indexes
--in order to create the columnstore index. 

DROP INDEX SimpleTable.nc1_simple;
DROP INDEX SimpleTable.nc2_simple;

--Convert the rowstore table to a columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON SimpleTable; 
GO

This example explains how to convert a large fact table from a rowstore table to a columnstore table.

To convert a rowstore table to a columnstore table.

  1. First, create a small table to use in this example.

    --Create a rowstore table with a clustered index and a non-clustered index.
    CREATE TABLE MyFactTable (
        ProductKey [int] NOT NULL,
        OrderDateKey [int] NOT NULL,
         DueDateKey [int] NOT NULL,
         ShipDateKey [int] NOT NULL )
    )
    WITH (
        CLUSTERED INDEX ( ProductKey )
    );
    
    --Add a non-clustered index.
    CREATE INDEX my_index ON MyFactTable ( ProductKey, OrderDateKey );
    
  2. Drop all non-clustered indexes from the rowstore table.

    --Drop all non-clustered indexes
    DROP INDEX my_index ON MyFactTable;
    
  3. Drop the clustered index.

    • Do this only if you want to specify a new name for the index when it is converted to a clustered columnstore index. If you do not drop the clustered index, the new clustered columnstore index will have the same name.

      System_CAPS_noteNote

      The name of the index might be easier to remember if you use your own name. All rowstore clustered indexes use the default name which is 'ClusteredIndex_<GUID>'.

    --Process for dropping a clustered index.
    --First, look up the name of the clustered rowstore index.
    --Clustered rowstore indexes always use the DEFAULT name ‘ClusteredIndex_<GUID>’.
    SELECT i.name 
    FROM sys.indexes i 
    JOIN sys.tables t
    ON ( i.type_desc = 'CLUSTERED' ) WHERE t.name = 'MyFactTable';
    
    --Drop the clustered rowstore index.
    DROP INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09 ON MyDimTable;
    
  4. Convert the rowstore table to a columnstore table with a clustered columnstore index.

    --Option 1: Convert to columnstore and name the new clustered columnstore index MyCCI.
    CREATE CLUSTERED COLUMNSTORE INDEX MyCCI ON MyFactTable;
    
    --Option 2: Convert to columnstore and use the rowstore clustered 
    --index name for the columnstore clustered index name.
    --First, look up the name of the clustered rowstore index.
    SELECT i.name 
    FROM sys.indexes i
    JOIN sys.tables t 
    ON ( i.type_desc = 'CLUSTERED' )
    WHERE t.name = 'MyFactTable';
    
    --Second, create the clustered columnstore index and 
    --Replace ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
    --with the name of your clustered index.
    CREATE CLUSTERED COLUMNSTORE INDEX 
    ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
     ON MyFactTable
    WITH DROP_EXISTING = ON;
    

To convert a columnstore table to a rowstore table with a clustered index, use the CREATE INDEX statement with the DROP_EXISTING option.

CREATE CLUSTERED INDEX ci_MyTable 
ON MyFactTable
WITH ( DROP EXISTING = ON );

This example creates a nonclustered columnstore index on a rowstore table. Only one columnstore index can be created in this situation. The columnstore index requires extra storage since it contains a copy of the data in the rowstore table. This 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

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.

The following example creates a filtered nonclustered columnstore index on the Production.BillOfMaterials table in the AdventureWorks2012 database. The filter predicate can include columns that are not key columns in the filtered index. The predicate in this example selects only the rows where EndDate is non-NULL.

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

Applies to: SQL Server 2012 through SQL Server 2014.

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.

Bookmark link 'TOP' is broken in topic '{"project_id":"7d6ffa79-2ddc-4606-b774-a6a5d46784cd","entity_id":"7e1793b3-5383-4e3d-8cef-027c0c8cb5b1","entity_type":"Article","locale":"en-US"}'. Rebuilding the topic '{"project_id":"7d6ffa79-2ddc-4606-b774-a6a5d46784cd","entity_id":"7e1793b3-5383-4e3d-8cef-027c0c8cb5b1","entity_type":"Article","locale":"en-US"}' may solve the problem.

To rebuild a partition of a large clustered columnstore index, use ALTER INDEX REBUILD. You can also use ALTER INDEX with the REBUILD option to rebuild all partitions, or to rebuild a non-partitioned clustered columnstore index.

ALTER INDEX cci_fact3 
ON fact3
REBUILD PARTITION = 12;

There are two ways to rebuild the full clustered columnstore index. You can use CREATE CLUSTERED COLUMNSTORE INDEX, or ALTER INDEX and the REBUILD option. Both methods achieve the same results.

--Determine the Clustered Columnstore Index name of MyDimTable.
SELECT i.object_id, i.name, t.object_id, t.name 
FROM sys.indexes i 
JOIN sys.tables t
ON (i.type_desc = 'CLUSTERED COLUMNSTORE')
WHERE t.name = 'RowstoreDimTable';

--Rebuild the entire index by using CREATE CLUSTERED INDEX.
CREATE CLUSTERED COLUMNSTORE INDEX my_CCI 
ON MyFactTable
WITH ( DROP_EXISTING = ON );

--Rebuild the entire index by using ALTER INDEX and the REBUILD option.
ALTER INDEX my_CCI
ON MyFactTable
REBUILD PARTITION = ALL
WITH ( DROP_EXISTING = ON );


Community Additions

ADD
Show:
© 2015 Microsoft