sql-servert-sqlcolumnstore

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

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 type: memberOf nvarchar(max).

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

Solution

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