ALTER SCHEMA in Synapse Warehouse

BiJoe — Mon, 26 Feb 2024 07:21:55 GMT

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

Re: ALTER SCHEMA in Synapse Warehouse

Anonymous — Mon, 26 Feb 2024 08:48:53 GMT

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.

topic ALTER SCHEMA in Synapse Warehouse in Data Warehouse

ALTER SCHEMA in Synapse Warehouse

Re: ALTER SCHEMA in Synapse Warehouse