CODEWITHSUNDEEP
azuresql-data-warehouseazure-synapse
kee
kee

Reputation: 11629

Azure SQL Data Warehouse: max size of varchar type

I am new to Azure and I have this field in my table in Azure SQL Data Warehouse:

[AnnotationText] varchar(MAX) NULL,

Based on what I read from https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017:

But what I am seeing is the size of 8000 with this type of error message:

Too long string in column [-1]: Actual len = [11054]. MaxLEN=[8000]

I tried to hardcode the size but any number bigger than 8000 isn't working as written in the document.

Also I found some docs saying that HEAP or CLUSTERED INDEX type should be used in the table creation but it didn't help either

Any idea what went wrong?

Upvotes: 1

Views: 11976

Answers (2)

Tero Kruth
Tero Kruth

Reputation: 41

I'm using Databricks to insert data into Azure DW (Synapse) using Polybase. I have a nvarchar(500) column in my dataset and it failed having the same problem. I found that in my case the breaking point is somewhere between 255 and 300 characters. I ended up just substringing that column to 250 characters, because the content was not important for reporting purposes.

Upvotes: 1

Ron Dunn
Ron Dunn

Reputation: 3078

ADF documents that (max) data types are not supported for Polybase.

One commonly used technique is to split the file in ADF, using Polybase to bulk load the LOB data, then an alternative technique to add the LOB data later. Another technique - faster - is to split the column using ADF, and reassemble it using a view layered over the external table at the point of ingestion.

A better approach may be to question why LOB data is required in the data warehouse. Is there an alternative approach that could be used, depending on the type of the LOB? For example, if the LOB represents a document, could it be externalised to a blob store with a link in the DW table?

Upvotes: 1

Related Questions