How to Lower the String Size in Fabric Tables from Varchar (8000)

[Jitendra123]

I have created a Table through DataFlows in Fabric Warehouse, by default it took varchar (8000) for Text feilds

but 

At times, for large queries/Viewsa, it will run into an error with the maximum in memory tablesize is not big enough but a lot of the space reserved is not actually required but is due to the field definitions.

Need Help, how to lower/Minimize the String Size in Text Columns

[Anonymous · Accepted solution]

Hi @Jitendra123 

Due to restrictions, you cannot use ALTER TABLE to modify it in the warehouse through SQL statements.

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#limitations

In my testing, I tried to solve the problem by creating a new table. This method creates a new table structure to replace the original table structure, and then migrates the data to the new table. For example:

CREATE TABLE New_TABLE (
    id INT,
    name VARCHAR(200), --New DataTypes
    Job VARCHAR(500)
);

INSERT INTO TesT_TABLE (id, name, Job)
SELECT Old_Column1, Old_Column2, Old_Column3
FROM Old_table;

DROP TABLE Old_table;

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nilendraFabric · Accepted solution]

Hi @Jitendra123 

Currently, Microsoft Fabric’s data warehouse has a limitation on varchar data types, enforcing a maximum length of around 8,000 characters for varchar fields. This limitation can cause issues with large queries or views, as you’ve experienced.
Unfortunately, there is no direct way to alter the varchar size in Fabric tables created through DataFlows. The system automatically assigns varchar(8000) to text fields for compatibility and to avoid potential data truncation. However, there are a few strategies you can consider to address this issue:
1. Use Lakehouse Instead
For columns that genuinely require more than 8,000 characters, consider using a Lakehouse instead of a warehouse. Lakehouses in Fabric can handle larger text fields without the 8,000-character limitation.

if this helps please accept the solution.

[Anonymous · Accepted solution]

Hi @Jitendra123 

Due to restrictions, you cannot use ALTER TABLE to modify it in the warehouse through SQL statements.

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#limitations

In my testing, I tried to solve the problem by creating a new table. This method creates a new table structure to replace the original table structure, and then migrates the data to the new table. For example:

CREATE TABLE New_TABLE (
    id INT,
    name VARCHAR(200), --New DataTypes
    Job VARCHAR(500)
);

INSERT INTO TesT_TABLE (id, name, Job)
SELECT Old_Column1, Old_Column2, Old_Column3
FROM Old_table;

DROP TABLE Old_table;

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.