9

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
alexithymia
  • 317
  • 2
  • 5
  • 18

3 Answers3

13

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.

Community
  • 1
  • 1
Rigerta
  • 3,959
  • 15
  • 26
  • Than means that i have to create a nonclustered columnstore index? – alexithymia Mar 19 '18 at 15:29
  • 5
    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. – Rigerta Mar 19 '18 at 16:04
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

Jimbo
  • 2,529
  • 19
  • 22
  • 1
    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._ – alexithymia Mar 19 '18 at 15:13
  • `CREATE CLUSTERED COLUMNSTORE INDEX cci ON algtable_20180319_bck (displayname, alias, firstname, lastname, exaddress)` – alexithymia Mar 19 '18 at 15:14
0

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

Apratim Mukherjee
  • 1