Forum Discussion
SQL ALTER command
Hi frithjof_v If you are using a Lakehouse, this is a case of the Fabric runtime version 1.1 vs 1.2 in a workspace. If you try using ALTER TABLE in a Notebook in a workspace with Fabric 1.1 (spark 3.3) then the ALTER TABLE is inconsistent and doesn't sync properly with the sql endpoint. If you use Fabric runtime 1.2 (spark 3.4) then ALTER TABLE should work.
It works for me, I can use:
%%sql
ALTER TABLE dimproducts
ADD COLUMN iscurrent int
and it's sync with the sql endpoint and the default semantic model
Hi frithjof_v , AndyDDC
We have an update from internal team -Note that there are two different SQL dialects in use in Fabric. In a Spark notebook you use Spark SQL. In the SQL Endpont you use T-SQL.
Inorder to clear everyone's confusion - we can use Alter commad in Notebook as it uses Spark SQL, but not T-SQL.
These limitations are with T-SQL - T-SQL surface area - Microsoft Fabric | Microsoft Learn
Hope this is helpful. Please let me know incase of further queries.
Hi frithjof_v ,
Thanks for using Fabric Community.
As per my knowledge though ALTER TABLE ADD COLUMN commands work, these are not recommended to use.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.
Hi frithjof_v ,
As per the documentation -
This command is also not executable in SQL Endpoint.
Without official confirmation this is not a recommended way to use.
Hope this is helpful.Confirmation would be good. But that documentation is specifying Warehouse and not Lakehouse. You can't modify a table schema or data using the Lakehouse SQL Endpoint anyway and that's by design.
but issuing ALTER TABLE via Notebook on a Lakehouse table should be supported in Fabric runtime 1.2. Hoping MS can clarify
Can you please link to that documentation? I believe it needs updating to differentiate between Warehouse (SQL) and Spark.
Hi frithjof_v , AndyDDC
We have an update from internal team -Note that there are two different SQL dialects in use in Fabric. In a Spark notebook you use Spark SQL. In the SQL Endpont you use T-SQL.
Inorder to clear everyone's confusion - we can use Alter commad in Notebook as it uses Spark SQL, but not T-SQL.
These limitations are with T-SQL - T-SQL surface area - Microsoft Fabric | Microsoft Learn
Hope this is helpful. Please let me know incase of further queries.