How to increase size of varchar which is already set to 8000 and if we try to set as varchar(max) it is showing error likeThe statement failed. Column 'column name' has a data type that cannot participate in a columnstore index.

Question

using these to run pipelines

Answer 1

The error you're encountering is because you're trying to use a column with a VARCHAR(MAX) data type in a columnstore index, which is not supported. Columnstore indexes have some restrictions on the data types they can work with, and VARCHAR(MAX) is not one of them.

-- Add a new column with the VARCHAR(MAX) data type to store larger text data
ALTER TABLE your_table
ADD new_column_name VARCHAR(MAX);

-- Update the new column with the data from the existing column
UPDATE your_table
SET new_column_name = old_column_name;

-- Temporarily drop the columnstore index that includes the old column
DROP INDEX index_name ON your_table;

-- Remove the old column from the table
ALTER TABLE your_table
DROP COLUMN old_column_name;

-- Recreate the columnstore index without including the new VARCHAR(MAX) column
-- Make sure to include only the columns that are supported by columnstore indexes
CREATE CLUSTERED COLUMNSTORE INDEX index_name ON your_table
(
  -- Include supported columns here, excluding the new VARCHAR(MAX) column
);

This code block will guide you through adding a new column with the VARCHAR(MAX) data type, copying data from the old column to the new one, dropping the columnstore index, removing the old column, and recreating the columnstore index without the new VARCHAR(MAX) column.

Answer 2

Hi @Priya

Refer to this doc: COLUMNSTORE INDEX (Limitations and restrictions)

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

• ntext, text, and image nvarchar(max), varchar(max), and varbinary(max)
• rowversion (and timestamp)
• sql_variant
• CLR types (hierarchyid and spatial types)
• xml
• uniqueidentifier

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Sedat SALMAN & @LiHongMSFT-4306

I have similar issue while creating a Brand New table with a sample of columns:

CREATE TABLE [Analytics].[ConsumerTkts](

  ID int not null,

  Ref NVARCHAR(100) NOT NULL,

  ...

  ...

  --Notes NVARCHAR(MAX) NULL,

  ...

)

WITH

(

DISTRIBUTION = HASH ( ID )

, CLUSTERED COLUMNSTORE INDEX

)

GO

Commands completed successfully.

But when trying below...

 ALTER TABLE [Analytics].[ConsumerTkts]

 ADD Notes NVARCHAR(MAX)

but probably the HASH DISTRIBUTION is not allowing. I'm getting below error:

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