Forum Discussion

fabanalytics's avatar
fabanalytics
Frequent Visitor
22 days ago
Solved

Varchar(MAX) in SQL Endpoint

I'm testing VARCHAR(MAX) support in a Fabric Lakehouse SQL Analytics Endpoint and discovered that it only worked after: 1. Enabling New Metadata Sync (Preview) 2. Creating a new Lakehouse after ena...
data warehouse
warehouse
  • jubinsoni's avatar
    jubinsoni
    19 days ago

    Hi fabanalytics​ ,

    Nice find. If you need this on an existing Lakehouse without moving data, one option is to create the new Lakehouse with the setting enabled and then add shortcuts in its Tables section pointing at the tables in your old Lakehouse. The data stays where it is, but the new SQL endpoint picks them up through the new metadata sync. Worth testing whether VARCHAR(MAX) comes through on shortcut tables the same way.

    To confirm what each endpoint is actually exposing, this quick query on both makes the comparison easy.

    SELECT t.name AS table_name, c.name AS column_name, ty.name AS data_type, c.max_length

FROM sys.columns c

JOIN sys.tables t ON c.object_id = t.object_id

JOIN sys.types ty ON c.user_type_id = ty.user_type_id

WHERE ty.name LIKE '%char%'

ORDER BY t.name, c.name;

    A max_length of -1 means VARCHAR(MAX). Since the new sync is still in preview, I'd keep the old Lakehouse around and only point production reports at the new one once you're happy it behaves.

ShivekMaharaj's avatar
ShivekMaharaj
Icon for Community Champion rankCommunity Champion
19 days ago

Hi fabanalytics​,

I think there are two separate things here.

Microsoft does explicitly document that the new metadata sync is Preview and only applies to SQL analytics endpoints created after the workspace setting is enabled. Existing endpoints remain on the legacy metadata-sync implementation.

So on that part, yes - there is currently no documented in-place switch that moves an existing Lakehouse SQL analytics endpoint onto the new metadata-sync engine.

However, I would be careful about tying that directly to VARCHAR(MAX).

Microsoft’s current Lakehouse SQL analytics endpoint documentation is supported for SQL analytics endpoints of mirrored items and Fabric databases, but not for Lakehouses.

The current Fabric data type mapping documentation also still maps large Lakehouse STRING/VARCHAR values to VARCHAR(8000), not VARCHAR(MAX).

That makes your test result very interesting.

I would verify exactly what the new endpoint is exposing by checking sys.columns. Microsoft notes that max_length = -1 represents VARCHAR(MAX).

For example, if the column really appears there with max_length = -1 on the new Lakehouse endpoint, then you may be seeing Preview behavior that is not yet reflected in the public datatype documentation.

If that is the case, I would not rely on it as a documented production capability yet.

So the current documented position appears to be:

  • New Metadata Sync: only new endpoints after enablement
  • Existing endpoints: remain on legacy metadata sync
  • Lakehouse VARCHAR(MAX): still documented as unsupported


If you can share what sys.columns reports for the same column on the old and new endpoints, that would help distinguish a genuine datatype change from a difference in query behavior.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.