Forum Discussion
How to Handle Complex Stored Procedures with DML and Cross-References in Microsoft Fabric?
Thanks v-veshwara-msft Yes, I'm aware of the Data Warehouse functionality, and it's part of my current design. However, it doesn't offer all the SQL features we need, which is why I’ve been exploring options with Fabric SQL Database.
That said, I’ve now got answers to the questions I had:
- Is there a better way to handle this scenario in Microsoft Fabric?
Not at the moment. The Data Warehouse remains the preferred option, even though it doesn’t support traditional indexes. Internally, it still helps improve performance over time through the use of statistics. While statistics aren't the same as indexes, they do assist the query optimizer in execution planning, which can lead to performance improvements. For enforcing uniqueness (like primary keys), the current workaround is to adjust stored procedures to ensure only unique records are ingested. While not ideal, this approach works for our current use case. - Can the SQL Endpoint be enabled to perform DML operations (writes)?
As of now, this isn't on the roadmap.
- Is there a better way to handle this scenario in Microsoft Fabric?
Hi aditridant ,
Thanks for posting in Microsoft Fabric Community.
Regarding your scenario - where the stored procedure needs to perform cross-referencing between tables (such as across Silver and Gold layers) along with DML operations - it’s worth noting that Microsoft Fabric does support cross-database and cross-warehouse queries within the same workspace.
This means you can:
-
Join tables from different SQL Databases or Warehouses using three-part naming (DatabaseName.SchemaName.TableName)
-
Perform DML operations like INSERT INTO ... SELECT FROM across these items
-
Include this logic inside stored procedures, provided all referenced objects are part of the same workspace.
For example:
SELECT *
FROM My_Lakehouse.dbo.Affiliation;
To enable this, ensure the required databases or warehouses are added to Object Explorer using the + Warehouses action. Once added, they can be referenced directly in your SQL queries and stored procedures.
Please follow this link for step by step procedure: Query the SQL Analytics Endpoint or Warehouse - Microsoft Fabric | Microsoft Learn
If your current setup spans multiple workspaces, or if you're using the SQL Endpoint (read-only) for part of the logic, those limitations would still apply. In such cases, using Data Pipelines or Notebooks for the write operations remains a valid alternative.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.
Thank you.
Thanks v-veshwara-msft Yes, I'm aware of the Data Warehouse functionality, and it's part of my current design. However, it doesn't offer all the SQL features we need, which is why I’ve been exploring options with Fabric SQL Database.
That said, I’ve now got answers to the questions I had:
- Is there a better way to handle this scenario in Microsoft Fabric?
Not at the moment. The Data Warehouse remains the preferred option, even though it doesn’t support traditional indexes. Internally, it still helps improve performance over time through the use of statistics. While statistics aren't the same as indexes, they do assist the query optimizer in execution planning, which can lead to performance improvements. For enforcing uniqueness (like primary keys), the current workaround is to adjust stored procedures to ensure only unique records are ingested. While not ideal, this approach works for our current use case. - Can the SQL Endpoint be enabled to perform DML operations (writes)?
As of now, this isn't on the roadmap.
Hi aditridant ,
Thanks for the detailed response and for confirming how you're approaching it.
Just to add - while Fabric Warehouse is your current choice, it's good to be aware that it does support defining PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints, but only with some conditions. Specifically, they must be declared as NONCLUSTERED and NOT ENFORCED. This means the constraints are available in metadata and can help with modeling (like relationships in Power BI), but the system doesn’t actually enforce them. So, duplicates can still be inserted unless handled manually.
Ref: Primary, Foreign, and Unique Keys - Microsoft Fabric | Microsoft Learn
That aligns with your current approach of using stored procedures to make sure only unique data gets in. It’s a practical workaround for now given the platform’s behavior.
Also, as you noted, the SQL Endpoint remains read-only and can’t be used for DML operations. There’s no support for enabling writes through it at this point.
Thnaks again for sharing and please consider marking your reply as the accepted solution to help others with similar queries. Also, please continue using the Fabric Community for any further questions. We are happy to help.
Regards,
Vinay kumar.
- Is there a better way to handle this scenario in Microsoft Fabric?