How to store the value of a unicode source column greater than 16000 characters in a dedicated SQL pool table

Question

There is a unicode source column that is variable in column length since it contains the body of a news article. Therefore, there is no limit to the length of this column. In order to utilize this information, I need to load it into a hash distribution type synapse table. The MAX data length cannot be set on a column of an nvarchar datatype in a hash distributed table. Is there a way to accomplish my requirements now?

Answer 1

It is not possible to set the MAX data length on a column of an NVARCHAR datatype in a hash-distributed table.

One method to specify the maximum data length for a column of type NVARCHAR in a hash-distributed table is to use the NVARCHAR(MAX) data type.

This data type can accommodate Unicode string data of up to 2 GB in size.

If the column has any constraint, such as a default or check constraint, you will need to drop the constraint from the column before changing its size. After altering the column size, you can add the constraint back to the column using the following steps:

-- Drop the constraint 
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

-- Alter the column size 
ALTER TABLE table_name ALTER COLUMN table_column NVARCHAR(MAX);

-- Add the constraint 
ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT ('') FOR table_column;

I have tried the below example:

CREATE TABLE NewsArticle (
    ArticleID INT ,
    Headline NVARCHAR(100),
    Body1 NVARCHAR(4000),
    Body2 NVARCHAR(4000),
    Body3 NVARCHAR(4000),
    Body4 NVARCHAR(4000),
    Body5 NVARCHAR(4000),
    Body6 NVARCHAR(4000),
    Body7 NVARCHAR(4000),
    Body8 NVARCHAR(4000),
    Body9 NVARCHAR(4000),
    Body10 NVARCHAR(4000)
)
WITH (
    DISTRIBUTION = HASH(ArticleID)
);

In the above code, you can split the body of the news article into multiple columns and distribute them across the hash distribution. In this way, you can still store the complete body text while utilizing the hash distribution feature.

Results: