Search code examples
stringazureazure-synapse

Replacing nvarchar(max) in Azure synapse table

While importing data from a csv file into synapse table, getting the following error:

The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.,Source=System.Data,''Type=System.InvalidOperationException,Message=String or binary data would be truncated.,Source=System.Data

The source column datatype is nvarchar(max) & in synapse, nvarchar can't be set to max so I am using nvarchar(4000). However, even 4000 is not enough and I'm still getting this error. (As far as I know, this mismatch of character limitation is causing the issue?)

When specifying the datatype nvarchar(max) in synapse, I get the following error: The statement failed. Column 'Preferences' has a data type that cannot participate in a columnstore index.

Any workaround for this issue, which is accepted in Synapse?

Solution

  • The default structure of a table in Azure synapse dedicated SQL pool is clustered column store, which does not support nvarchar(max). You can create the table as heap, and so use this data type.

      CREATE TABLE MyTable   
  (  
    mycolumnnn1 nvarchar(max),  
    mycolumn2 int )  
WITH ( HEAP )  
;

    Please note that this may have performance affects, and also, generally , such large text fields do not fit well in a data warehouse.