Columnar Indexes – Declarations, Implementations, and Restrictions

Background

Wanted to start a post that lists some of the operations that we are able to perform again a traditional Row based table, but are not currently supported against a Column Indexed table.

Index Definition

 

Topic Defintion Message v2014 v2016
Clustered Column Index – Other indexes can exist CREATE NONCLUSTERED COLUMNSTORE INDEX [INDX_COLUMNSTORE_CLUSTERED_SalesAmount]
ON [dbo].[FactResellerSales.ColumnStore]
(
[SalesAmount]
)
Msg 35303

CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.

No No
NonClustered Column Index – Other indexes can exist CREATE NONCLUSTERED COLUMNSTORE INDEX [INDX_COLUMNSTORE_CLUSTERED_SalesAmount]
ON [dbo].[FactResellerSales.ColumnStore]
(
[SalesAmount]
)
go
Yes Yes
Clustered Column Index – Explicit Column List CREATE CLUSTERED COLUMNSTORE INDEX [INDX_COLUMNSTORE_CLUSTERED]
ON [dbo].[FactResellerSales.ColumnStore]
Msg 35335, Level 15

CREATE INDEX statement failed because specifying a key list is not allowed when creating a clustered columnstore index. Create the clustered columnstore index without specifying a key list.

No No
Non Clustered Column Index – Explicit Column List CREATE NONCLUSTERED COLUMNSTORE INDEX [INDX_COLUMNSTORE_CLUSTERED_SalesAmount]
ON [dbo].[FactResellerSales.ColumnStore]
(
[SalesAmount]
)
Yes Yes
Order By CREATE NONCLUSTERED COLUMNSTORE INDEX [INDX_COLUMNSTORE_CLUSTERED_OrderDateKey]
ON [dbo].[FactResellerSales.ColumnStore]
(
[OrderDateKey] desc
)
Msg 35302, Level 15

CREATE INDEX statement failed because specifying sort order (ASC or DESC) is not allowed when creating a columnstore index. Create the columnstore index without specifying a sort order.

No No

 

 

DML Operations

 

Topic Defintion Message v2014 v2016
Clustered Column Index – Truncate Table truncate table [dbo].[FactResellerSales.ColumnStore] Yes Yes
NonClustered Column Index – Truncate Table truncate table [dbo].[FactResellerSales.ColumnStore]  Msg 35349, Level 16

TRUNCATE TABLE statement failed because table ‘FactResellerSales.ColumnStore.NC’ has a columnstore index on it. A table with a columnstore index cannot be truncated. Consider dropping the columnstore index then truncating the table.

No No
Clustered Column Index – Insert/Update/Delete delete top 1 [dbo].[FactResellerSales.ColumnStore] Yes Yes
Non Clustered Column Index – Insert/Update/Delete delete top 1 [dbo].[FactResellerSales.ColumnStore]

 

Msg 35330, Level 15, State 1, Line 10
DELETE statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, and then rebuilding the columnstore index after DELETE has completed.
No No

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s