ALTER SCHEMA in Synapse Warehouse

[BiJoe]

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? 

T-SQL surface area - Microsoft Fabric | Microsoft Learn

[v-nikhilan-msft · Accepted solution]

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.