Forum Discussion
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 enabling the setting
Existing Lakehouses didn't seem to pick up the capability.
Is this officially expected, or is there a migration/upgrade path for existing SQL Analytics Endpoints?
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.
7 Replies
Yes, this is expected behavior. Enabling New Metadata Sync only applies to endpoints created after the setting is enabled. Existing endpoints continue using the old sync engine, so there isn't currently a way to switch an existing one over.
That's also why you're seeing VARCHAR(MAX) work with the newly created Lakehouse but not the existing one.
For now, recreating the Lakehouse is the practical option. If the existing Lakehouse already has data, I wouldn't try to change it in place. I would create the new Lakehouse with the setting enabled first and then move or reference the existing Delta tables as appropriate.
Microsoft has mentioned an upgrade path for existing endpoints, but I would check the current roadmap/documentation before depending on that.
- ShivekMaharaj
Community Champion
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.
v-kathullacCommunity Support
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya- v-kathullac
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya Just want to clarify, VARCHAR(MAX) doesnt get enabled with the newly created Lakehouse. The New metadata Sync must be enabled first before the lakehouse is created.
The New metadata sync documentation doesnt say it is a requirement for VARCHAR(MAX).
jubinsoniContinued Contributor
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.
Yes, I verified that the VARCHAR(MAX) comes through on shortcut tables. At this point we are in development phase, so we will recreate the lakehouse after the metadata sync enabled.
