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

Question

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?

Proxy

Answer 1

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.

Pydna

Than means that i have to create a nonclustered columnstore index? – Proxy

No, it means you can't create a columnstore index on that column at all. You could try to change the type from nvarchar(max) to limit it, for example set the length to 4000. Then you can create a columnstore index on it. – Pydna

Answer 2

You'll need to specify the participating columns individually, excluding memberOf and any other columns that can't be used in a columnStore index

Honky

I tried what you suggested and I got the message: The 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. – Proxy

CREATE CLUSTERED COLUMNSTORE INDEX cci ON algtable_20180319_bck (displayname, alias, firstname, lastname, exaddress) – Proxy

Answer 3

just specify the length of the varchar variables instead of using max. Did the trick for me!

Samaria

Answer 4

I was creating tables in synapase, we have take care max should avoided and give number value to avoid error.

enter code `CREATE TABLE [dbo].[TripData](
[medallion] [varchar](100) NULL,
[hack_license] [varchar](100) NULL,
[vendor_id] [varchar](100) NULL,
[rate_code] [int] NULL,
[store_and_fwd_flag] [varchar](100) NULL,
[pickup_datetime] [datetime] NULL,
[dropoff_datetime] [datetime] NULL,
[passenger_count] [int] NULL,
[trip_time_in_secs] [int] NULL,
[trip_distance] [float] NULL,
[pickup_longitude] [varchar](100) NULL,
[pickup_latitude] [varchar](100) NULL,
[dropoff_longitude] [varchar](100) NULL,
[dropoff_latitude] [varchar](100) NULL

) `here

Frumpish