Export (0) Print
Expand All
Expand Minimize

CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL)

SQL Server 2014

Creates an in-memory clustered columnstore index on a SQL Server table. Use a clustered columnstore index to improve data compression and query performance for data warehousing workloads that primarily perform bulk loads and read-only queries. Since the clustered columnstore index is updateable, the workload can perform some insert, update, and delete operations.

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

For more information, see these topics:

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

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 ( 
           DROP_EXISTING = ON
           [ , MAXDOP = max_degree_of_parallelism ]
    )
    [ ON <on_option> ]
[ ; ]

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

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 of the index. If table has a clustered index, or a clustered columnstore index, then index_name must match the name of the existing index

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.

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

DROP_EXISTING = ON

Required when the table has an existing clustered index or clustered columnstore index. The existing index and the new index must have the same name.

For DROP_EXISTING = OFF, 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.

Requires ALTER permission on the table.

  • Use CREATE CLUSTERED COLUMNSTORE INDEX to convert a heap or clustered index to a clustered columnstore index; this changes the table from a rowstore table to a columnstore table. Or, use this to rebuild an existing clustered columnstore index.

  • A table with a clustered columnstore index cannot have any type of nonclustered index.

  • Clustered columnstore indexes are only available in the Enterprise, Developer, and Evaluation editions.

Clustered columnstore indexes are only available in the Enterprise, Developer, and Evaluation editions.

You must drop all nonclustered indexes on the table before creating a clustered columnstore index.

SQL Server supports partition switching for clustered columnstore indexes. It first partitions the data, and then compresses the data per partition into the columnstore.

You can create a clustered columnstore index on a temporary table.

Columnstore index is:

  • Is The only allowable index on the table

  • Cannot have unique constraints, primary key constraints, or foreign key constraints.

  • Are only available in the Enterprise, Developer, and Evaluation editions.

  • Cannot have more than 1024 columns.

  • 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 ) ]

  • 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

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

  • Cursors are not supported on a table which has a clustered columnstore index or on an indexed view created on a table with a clustered columnstore index.

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 clustered columnstore index

Convert a heap to a clustered columnstore index

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

Convert a clustered index to a clustered columnstore index with the same name.

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

D. Convert a table that has nonclustered indexes to a clustered columnstore index.

The following example creates a simple rowstore table, stored as a heap, with two nonclustered indexes, and then demonstrates the syntax of converting the rowstore table to a columnstore table. The nonclustered indexes must be dropped before you create the clustered columnstore index. They cannot be dropped with the DROP_EXISTING option.

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

--Before creating the clustered columnstore index, 
--all nonclustered indexes must be dropped. 

DROP INDEX SimpleTable.nc1_simple;
DROP INDEX SimpleTable.nc2_simple;
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON SimpleTable; 
GO

Convert a large fact table from rowstore to columnstore

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.

      Note Note

      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;
    

B. Defragment a clustered columnstore index

Rebuild a specific partition.

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;

Rebuild the entire clustered columnstore index

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

Reorganize the clustered columnstore index

Reorganizing a clustered columnstore index forces all CLOSED rowgroups in the deltastore to be compressed with columnstore compression and moved into the columnstore. This is not required, but can improve performance after a large load in which CLOSED rowgroups are not moving into the columnstore fast enough. For more information, see Columnstore Indexes Described.

ALTER INDEX cci_index ON table1 REBUILD;

C. Convert columnstore tables to rowstore tables

Convert a columnstore table to a rowstore table with a clustered index

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

Convert a columnstore table to a rowstore heap

To convert a columnstore table to a rowstore heap, simply drop the clustered columnstore index.

DROP INDEX MyCCI 
ON MyFactTable;

[ TOP ]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft