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

Question

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

Vivek Komarla Bhaskar 956

Hi, 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?

Vinodh247 40,141 Reputation points MVP Volunteer Moderator

2024-02-27T15:42:18.6566667+00:00

Hi Vivek Komarla Bhaskar: Please confirm if we are still connected on this discussion. Let me know if you need more info or help.
Anonymous

2024-02-28T13:31:18.4+00:00
@Vivek Komarla Bhaskar @Vivek Komarla Bhaskar

You can use a two-step process:

Load the data into a staging table: Create a staging table with a VARCHAR(MAX) column to hold your article body. Load your data into this staging table.

CREATE TABLE StagingTable ( ArticleBody VARCHAR(MAX) )

Move the data to the final table: Once the data is in the staging table, you can then insert it into your final hash-distributed table. If the length of the data in the ArticleBody column exceeds the maximum length allowed in your final table, you will need to truncate or otherwise handle the data to fit.

INSERT INTO FinalTable (ArticleBody) SELECT LEFT(ArticleBody, 4000) -- or whatever your column length is FROM StagingTable

This way, you can load data of any length into your staging table, and then ensure it fits into your final table when moving the data over. Please note that this is a workaround and you may lose some data if the length of the ArticleBody exceeds the maximum length of the column in the final table. If preserving all data is critical, you may need to reconsider the design of your database or the distribution type of your table. For example, you could use a replicated table instead of a hash-distributed table if the size of the table is not too large. Replicated tables do not have the same length restrictions as hash-distributed tables. However, they consume more resources and are not suitable for very large tables.

1 answer

Your answer

Vinodh247 40,141 Reputation points MVP Volunteer Moderator

2024-02-27T15:42:18.6566667+00:00

Hi Vivek Komarla Bhaskar: Please confirm if we are still connected on this discussion. Let me know if you need more info or help.
Anonymous

2024-02-28T13:31:18.4+00:00

@Vivek Komarla Bhaskar @Vivek Komarla Bhaskar

You can use a two-step process:

Load the data into a staging table: Create a staging table with a VARCHAR(MAX) column to hold your article body. Load your data into this staging table.

CREATE TABLE StagingTable ( ArticleBody VARCHAR(MAX) )

Move the data to the final table: Once the data is in the staging table, you can then insert it into your final hash-distributed table. If the length of the data in the ArticleBody column exceeds the maximum length allowed in your final table, you will need to truncate or otherwise handle the data to fit.

INSERT INTO FinalTable (ArticleBody) SELECT LEFT(ArticleBody, 4000) -- or whatever your column length is FROM StagingTable

This way, you can load data of any length into your staging table, and then ensure it fits into your final table when moving the data over. Please note that this is a workaround and you may lose some data if the length of the ArticleBody exceeds the maximum length of the column in the final table. If preserving all data is critical, you may need to reconsider the design of your database or the distribution type of your table. For example, you could use a replicated table instead of a hash-distributed table if the size of the table is not too large. Replicated tables do not have the same length restrictions as hash-distributed tables. However, they consume more resources and are not suitable for very large tables.

Answer 1

Vinodh247 40,141 MVP Volunteer Moderator

Hi Vivek Komarla Bhaskar,

Thanks for reaching out to Microsoft Q&A.

one way to set the MAX data length on a column of an nvarchar datatype in a hash distributed table is to use the NVARCHAR(MAX) data type. This data type can store up to 2 GB of Unicode string data..

However, if the column has any constraint key like default, check, etc., you will need to drop the key constraint from the column before altering the size of the column and then add the constraint back to the column like below

-- Drop the key 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 key constraint 
ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT ('') FOR table_name;

Alternatively, you can also use the VARBINARY(MAX) data type but I am not sure if that is supported in hash distributed table.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Anonymous

2024-02-19T09:52:48.62+00:00

@Vinodh247 Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Vivek Komarla Bhaskar 956 Reputation points

2024-02-19T10:37:24.95+00:00

Hi @Vinodh247 The query I am asking is related to the Synapse dedicated SQL pool. Whether you've verified it or not, I can't create a datatype of length MAX if the table is hashed and without constraints. I would appreciate it if you could verify your end and let me know. The solution you suggested does not work.
Vinodh247 40,141 Reputation points MVP Volunteer Moderator

2024-02-20T05:26:35.0133333+00:00

Hi Vivek Komarla Bhaskar:

Apologies. I confused yours with another question.

Yes, it is not supported with HASH distributed tables. The default structure of a table in Azure synapse dedicated SQL pool is a clustered column store, which does not support nvarchar (max).

Alternatively, you can use NVARCHAR (4000) or VARCHAR (8000) instead of NVARCHAR (MAX) or VARCHAR (MAX) whenever possible.
Anonymous

2024-02-21T07:27:13.2333333+00:00

@Vivek Komarla Bhaskar
Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Vivek Komarla Bhaskar 956 Reputation points

2024-02-27T16:08:20.99+00:00

There is still no solution to my problem. Would you be able to suggest a recommendation to handle my scenario. How do I store data for a column with an undefined length that needs to be stored in one or more columns?

Share via

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

1 answer

Your answer