Forum Discussion
Varchar(MAX) in SQL Endpoint
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.
Continued ContributorHi 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.
