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?
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.