Column has a data type that cannot participate in a columnstore index

sql-server tsql columnstore
alexithymia picture alexithymia · Mar 19, 2018 · Viewed 11.6k times · Source

I want to create a clustered columnstore index in a table using the following query:

CREATE CLUSTERED COLUMNSTORE INDEX cci
ON agl_20180319_bck

And I am getting this error:

Msg 35343, Level 16, State 1, Line 6 The statement failed. Column 'memberOf' has a data type that cannot participate in a columnstore index. Omit column 'memberOf'.

The 'memberOf' is in this format: memberOf(nvarchar(max)).

How to overcome/ignore this error and what does it mean?

Answer

Rigerta picture Rigerta · Mar 19, 2018

As per documentation:

Columns that use any of the following data types cannot be included in a columnstore index:

nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 and prior versions, and nonclustered columnstore indexes)

Either change the type of the column (if you can) or just don't have a columnstore index on this specific column.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More