What is the difference between Azure Synapse Dedicated SQL Pools and Serverless SQL Pools?


Answer: Azure Synapse Dedicated SQL pools is based on the legacy on-prem MS SQL Server technology of the past, while Azure Synapse SQL Serverless Pools is a new technology built from the ground up by a different Microsoft team using a modern distributed query processing engine reading files directly on top of ADLS.

Based on these two SQL solutions having completely different architectures they have widely different capabilities, where Synapse Serverless SQL Pools have:
- Great support for legacy T-SQL
- Great integration with Power BI
- Good Support for reading open format files directly from ADLS (including Delta Lake)

However, Serverless SQL Pools has:
- NO support for data writing (no INSERT, UPDATE, DELETE, or MERGE capability), because all integration must happen externally on files.
- NO result caching, so dashboards or queries that are run multiple times will pay in time and money for each execution even if just run seconds ago.
- NO file caching, so cloud storage API can be high and flood the network and slow a larger installation