Forum Discussion
ALTER SCHEMA in Synapse Warehouse
I would like to transfer tables to a different schema than [dbo] after loading it to a Warehouse with Dataflow gen 2. Is it safe to run ALTER SCHEMA in a SQL query in from Fabric Synapse Warehouse?
Hi BiJoe
Thanks for using Fabric Community.While ALTER SCHEMA exists in Transact-SQL, it's not recommended to use it in a Fabric Synapse Warehouse for transferring tables between schemas. Here's why:
Limitations of ALTER SCHEMA in Fabric Synapse Warehouse:
- Limited Functionality: Unlike SQL Server, Fabric Warehouse has limited functionality for modifying schema objects like tables using ALTER SCHEMA.
- Compatibility Issues: Using ALTER SCHEMA might not be fully compatible with other data modification operations in Fabric Warehouse and could lead to unexpected behavior or errors.
I tried to repro using this SQL code:
The code ran successfully. The table's schema also got changed. But when Iam trying to load the data, its giving me an error.
So, I wouldnt suggest you to alter the schema. Instead create a clone of the table using the new schema. You can do this by following the below steps:For more information please refer to this link:
https://learn.microsoft.com/en-us/fabric/data-warehouse/clone-table
This will create a copy of the table. You can delete the dbo.Table after creating the clone.
Hope this helps. Please let me know if you have any further questions.
1 Reply
Hi BiJoe
Thanks for using Fabric Community.While ALTER SCHEMA exists in Transact-SQL, it's not recommended to use it in a Fabric Synapse Warehouse for transferring tables between schemas. Here's why:
Limitations of ALTER SCHEMA in Fabric Synapse Warehouse:
- Limited Functionality: Unlike SQL Server, Fabric Warehouse has limited functionality for modifying schema objects like tables using ALTER SCHEMA.
- Compatibility Issues: Using ALTER SCHEMA might not be fully compatible with other data modification operations in Fabric Warehouse and could lead to unexpected behavior or errors.
I tried to repro using this SQL code:
The code ran successfully. The table's schema also got changed. But when Iam trying to load the data, its giving me an error.
So, I wouldnt suggest you to alter the schema. Instead create a clone of the table using the new schema. You can do this by following the below steps:For more information please refer to this link:
https://learn.microsoft.com/en-us/fabric/data-warehouse/clone-table
This will create a copy of the table. You can delete the dbo.Table after creating the clone.
Hope this helps. Please let me know if you have any further questions.