Hi , I am Creating table in Synapse dedicated sql pool with Field of varchar(max) and it is taking clusterd columnstore index by default and throwing error as Varchar max filed can not be added in columnstore index. How should i use varchar (max) while creating table with other index other than columnstore index. Is there any way we can use varchar (max ) and We can disable Columnstore index or we can use other options. Note - We have Column in source which is having more than 8000 char so we need varchar(max) Thank you.

Eg : CREATE TABLE [dbo].[Test] ( [Id] [INT] NOT NULL ,[Skills] VARCHAR NULL WITH ( DISTRIBUTION = ROUND_ROBIN, CLUSTERED INDEX ( [Id] ASC ) )

Azure Synapse Columnstore index and Varchar(max)

Accepted answer

Nandan Hegde 29,896 Reputation points MVP

2022-08-12T16:05:05.49+00:00

Eg :

CREATE TABLE [dbo].[Test]

(
[Id] [INT] NOT NULL
,[Skills] VARCHAR NULL

WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED INDEX
(
[Id] ASC
)
)
Please sign in to rate this answer.

1 person found this answer helpful.
gajanan choure 51 Reputation points

2022-08-14T14:13:53.967+00:00

Hi Nandan ,
Thank you for your response.

We have tried with clustered index with similar syntax but still it is failing while copy data.

We are using copy data activity in which source is Delta table Databricks and sink is Synapse table.
Option used in copy data - upsert (as we are doing incremental load)

Error - column 'x' has a datatype that can not participate in columnstore index.
We are not creating table with columnstore index still getting this error.
Note - we have used Clustered index as u mentioned

AnnuKumari-MSFT 31,151 Reputation points Microsoft Employee

2022-08-23T06:25:08.777+00:00

Hi @gajanan choure ,

By default, dedicated SQL pool creates a clustered columnstore index when no index options are specified on a table

There are a few scenarios where clustered columnstore may not be a good option:

Columnstore tables do not support varchar(max), nvarchar(max), and varbinary(max). Consider heap or clustered index instead.

Columnstore tables may be less efficient for transient data. Consider heap and perhaps even temporary tables.

Small tables with less than 60 million rows. Consider heap tables.

Kindly refer to: Indexes on dedicated SQL pool tables in Azure Synapse Analytics

AnnuKumari-MSFT 31,151 Reputation points Microsoft Employee

2022-08-25T10:22:51.97+00:00

Hi @gajanan choure ,
Just checking in to see if the above answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
Sign in to comment

Azure Synapse Columnstore index and Varchar(max)

0 additional answers