CODEWITHSUNDEEP
sqlsql-serverindexingsql-server-2012clustered-index
Teja
Teja

Reputation: 13534

How to ignore the columns as part of columnstore index that cannot be participated because of data type issues

I have an existing table which has more than 100 columns in it. Now I am trying to create a clustered columnstore index on the same table but it throws me an error as some of the columns do not meet the data types criteria due to restrictions. Is there a way to ignore the columns that do not meet the Clustered ColumnStore Index requirements. Thank you.

SQL for creating CCSI :-



CREATE CLUSTERED COLUMNSTORE INDEX CCSI 
ON [Dbname].[dbo].[testtable] 
WITH ( DROP_EXISTING = OFF );

Error :-

The statement failed. Column 'Column1' has a data type that cannot participate in a columnstore index. Omit column 'Column1'.

Upvotes: 0

Views: 5286

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You cannot ignore columns while using Clustered ColumnStore Index. For selected columns you must go for Non Clustered ColumnStore Index as below: 

CREATE NONCLUSTERED COLUMNSTORE INDEX CCSI 
ON [Dbname].[dbo].[testtable] (Col1, Col2...required columns)
WITH ( DROP_EXISTING = OFF );

Upvotes: 0

Related Questions