Skip to article
Analytics Synapse Analytics

Synapse SQL

Synapse SQL is the T-SQL analytics capability in Azure Synapse Analytics. It includes dedicated SQL pools for provisioned warehousing and serverless SQL pools for on-demand lake queries, letting teams analyze structured and semi-structured data through familiar SQL endpoints inside a Synapse workspace.

Source: Microsoft Learn - Azure Synapse SQL architecture Reviewed 2026-05-27T07:47:08Z

Exam trap
Saying Synapse SQL without clarifying whether the workload is serverless, dedicated, or a mix of both.
Production check
Run az synapse sql pool list to identify dedicated pools and confirm which ones are paused, online, or scaling.
Practice this topic Microsoft Learn
Article details and learning context
Aliases
Synapse SQL engine, Azure Synapse SQL, Synapse serverless and dedicated SQL, SQL analytics in Synapse
Difficulty
intermediate
CLI mappings
7
Last verified
2026-05-27T07:47:08Z
Review level
field-manual-complete
Article depth
field-manual-complete
Learning paths Learn Analytics Engineering Pipeline Synapse SQL
Compare related concepts Search related resources Browse the glossary

Understand the concept

In plain English

Synapse SQL lets teams use T-SQL against analytical data inside a Synapse workspace. It has two main operating models. Serverless SQL pool queries files in the lake without standing up dedicated compute, so it is useful for exploration and data lake reporting. Dedicated SQL pool is provisioned warehouse capacity for modeled, loaded, high-throughput analytics. The same term can appear in architecture discussions, query tuning, BI connectivity, data governance, and cost reviews. The important question is always which SQL model is being used and what data path it touches.

Why it matters

Synapse SQL matters because it is often the boundary where raw lake data becomes trusted business reporting. Choosing serverless when the workload needs predictable warehouse performance can create slow dashboards and repeated scan costs. Choosing dedicated SQL pool for occasional exploration can waste provisioned capacity. Misunderstanding the model also leads to broken security, unsupported T-SQL assumptions, poor table distribution, and confusing cost ownership. Operators need to know whether a query is scanning files, reading warehouse tables, using external objects, or running through a paused dedicated pool. For learners, Synapse SQL is a practical lesson in cloud analytics tradeoffs: convenience, performance, storage, governance, and billing are all linked. That reuse matters when reporting demand grows faster than engineering headcount.

Technical context

In Azure architecture, Synapse SQL sits between storage, data modeling, BI tools, and orchestration. Serverless SQL reads external data through metadata, credentials, external data sources, and file formats; it charges by data processed. Dedicated SQL pool stores relational tables in distributed storage and uses compute nodes, distributions, statistics, workload management, and DWU capacity. Both connect to identity, firewall rules, private endpoints, monitoring, SQL scripts, and pipeline activities. Architects review Synapse SQL with lake layout, table design, security model, client location, and downstream reporting commitments.

Exam context

Compare with

Serverless SQL pool Dedicated SQL pool Synapse workspace

Where it is used

Where you see it

  1. In Synapse Studio, the SQL section separates serverless SQL resources, dedicated SQL pools, SQL scripts, databases, external tables, credentials, monitoring views, and linked query artifacts. during connection setup and support triage.
  2. In Azure CLI, az synapse sql pool list and show reveal dedicated pool names, resource IDs, SKU or DWU state, tags, provisioning status, and capacity review context. during development, review, deployment, and troubleshooting.
  3. In BI failures, SQL error text often exposes whether the issue is a paused dedicated pool, missing external object, bad lake path, or permission problem. during cost and availability reviews.

Common situations

  • Choose serverless SQL for governed exploration over partitioned lake files before committing to a loaded warehouse design.
  • Use dedicated SQL pool when enterprise dashboards need predictable concurrency, distribution design, and modeled relational tables.
  • Move expensive repeated serverless scans into curated tables or materialized warehouse structures after query patterns become stable.
  • Pause and resume nonproduction dedicated SQL pools around test windows to control capacity spend without deleting stored data.
  • Standardize SQL scripts, external objects, credentials, and monitoring evidence for regulated analytics environments.

Illustrative Azure scenarios

These examples show how the concept can affect design and operations. They are illustrative scenarios, not customer claims.

Scenario 01 Manufacturer splits exploration from governed warehouse reporting Scenario, objectives, solution, measured impact, and takeaway.
Scenario

An industrial equipment manufacturer had one Synapse workspace serving engineers, finance analysts, and plant managers. Ad hoc lake queries and board dashboards were being discussed as if they were the same Synapse SQL workload.

Goals
  • Keep executive production-margin dashboards under a 12-minute refresh window.
  • Allow engineers to explore machine telemetry without loading every experiment into the warehouse.
  • Reduce monthly SQL spend that came from duplicated scans and always-on test capacity.
  • Create a clear operating model for serverless and dedicated SQL usage.
Approach using Synapse SQL

The architecture team classified SQL workloads into serverless exploration, curated serverless views, and dedicated warehouse reporting. Engineers used serverless SQL over partitioned Parquet telemetry with approved external data sources. Finance dashboards moved to a dedicated SQL pool with hash-distributed fact tables, replicated dimensions, and scheduled statistics maintenance. Nonproduction dedicated pools were paused outside integration-test windows through Azure CLI. SQL scripts were stored in Git and deployed through Synapse pipelines, while CLI inventory attached pool state and SKU evidence to weekly cost reviews. Storage paths, credentials, and permissions were documented for both models.

Potential outcomes
  • Board dashboard refresh time improved from 31 minutes to 9 minutes after moving stable reporting to dedicated SQL design.
  • Serverless query spend dropped 34% after raw telemetry exploration was limited to partitioned folders and curated views.
  • Test pool capacity costs fell 41% because pools paused automatically after nightly validation.
  • Incident triage time fell from two hours to 35 minutes because operators could identify the SQL model immediately.
What to learn

Synapse SQL delivers better outcomes when teams explicitly choose serverless or dedicated behavior instead of treating all SQL analytics alike.

Scenario 02 City open-data office protects citizen dashboards from lake schema drift Scenario, objectives, solution, measured impact, and takeaway.
Scenario

A city open-data office published transportation, permits, and service-request dashboards from Synapse SQL. New CSV columns from agencies repeatedly broke reports hours before public releases.

Goals
  • Keep public dashboards available during weekly agency data drops.
  • Detect lake file schema changes before Power BI refresh failures reach residents.
  • Avoid provisioned warehouse cost for low-traffic exploratory datasets.
  • Preserve a reliable warehouse path for high-visibility permit metrics.
Approach using Synapse SQL

The team used Synapse SQL in two lanes. Low-traffic open datasets stayed on serverless SQL with external views that normalized agency file changes. High-visibility permit metrics loaded into a small dedicated SQL pool after validation. A pre-release pipeline ran serverless checks against new files, compared expected columns, and stopped publication when drift appeared. Azure CLI exported SQL script definitions, listed dedicated pool state, and resumed the permit pool only for scheduled refresh. External data sources used managed identity and a locked curated container rather than raw agency drop zones. The office added monitoring for failed refreshes and data scanned.

Potential outcomes
  • Public dashboard failures dropped from six per quarter to one minor delayed refresh.
  • Dedicated SQL runtime fell 63% because the permit pool resumed only for validation and refresh windows.
  • Schema drift was caught before publication in 11 of 12 agency feed changes.
  • Resident support tickets about missing permit data fell 48% during the next release cycle.
What to learn

Synapse SQL can balance open-data flexibility with dependable reporting when serverless validation and dedicated warehouse commitments are separated.

Scenario 03 Biotech research group keeps trial analytics auditable without slowing discovery Scenario, objectives, solution, measured impact, and takeaway.
Scenario

A biotech research group analyzed instrument outputs, sample metadata, and trial operations in Synapse. Scientists needed flexible SQL access, but compliance teams needed proof around curated trial reporting.

Goals
  • Give scientists fast SQL access to nonregulated experiment files.
  • Keep regulated trial metrics in a controlled, repeatable reporting model.
  • Capture evidence for who changed SQL scripts and which pool produced reports.
  • Reduce query failures caused by storage permission mismatches.
Approach using Synapse SQL

The platform team placed exploratory experiment data behind serverless SQL views in a research database and kept regulated trial metrics in a dedicated SQL pool with controlled tables. External data sources used scoped managed identity permissions, and SQL scripts were promoted from Git only after peer review. CLI commands listed dedicated pool state, exported published scripts, and checked workspace details before each monthly compliance package. The team also created validation queries that verified source folder permissions before trial refreshes. Scientists retained flexible SQL access to allowed folders, while compliance reports ran against modeled warehouse tables with documented lineage.

Potential outcomes
  • Trial report reruns became 100% reproducible across four monthly compliance submissions.
  • Scientist wait time for exploratory SQL access fell from three days to same-day approval for approved folders.
  • Storage permission failures fell from 14 per month to two after managed identity paths were documented.
  • Compliance evidence preparation dropped from five analyst-days to one analyst-day per submission.
What to learn

Synapse SQL is most valuable when flexible research access and controlled reporting are designed as different operating patterns.

Azure CLI

With Synapse SQL, I use Azure CLI to remove ambiguity between intent and deployed state. Portal pages can show a friendly workspace, but CLI proves which dedicated SQL pools exist, whether a pool is paused, what SKU or DWU is configured, which scripts are published, and which workspace a command targets. For serverless SQL, there is less direct pool lifecycle to manage, so CLI is still valuable for workspace, firewall, script, role, and evidence collection. In production, screenshots do not scale across subscriptions. CLI lets engineers inventory pools, pause or resume dedicated capacity, validate deployment drift, export SQL scripts, and attach clear evidence to incidents, audits, and cost reviews. It also keeps automation honest when pool states change between releases.

Useful for

  • List dedicated SQL pools in a workspace and export state, SKU, tags, and resource IDs for FinOps review.
  • Pause or resume a dedicated SQL pool during batch windows using a checked automation identity.
  • Show pool configuration before a dashboard incident to confirm whether capacity, provisioning, or state changed.
  • Create or update SQL scripts through deployment automation while preserving source-controlled review.
  • Inventory workspace, firewall, role, and SQL script settings that indirectly govern serverless SQL access.

Before you run a command

  • Confirm the tenant, subscription, resource group, workspace, and pool name because SQL pool names are reused across environments.
  • Distinguish serverless SQL from dedicated SQL pool; not every Synapse SQL concept has a direct pool lifecycle command.
  • Check permissions for Azure resource management, Synapse RBAC, and SQL-level access before troubleshooting mixed authorization failures.
  • Treat pause, resume, scale, and delete operations as production-impacting even when the command looks simple.
  • Use JSON output and explicit queries when exporting evidence for audit, cost, or deployment-drift review.

What the output tells you

  • The workspace and resource IDs prove whether you targeted the right Synapse boundary for the SQL workload.
  • Dedicated pool state shows whether compute is online, paused, provisioning, scaling, or unavailable for query execution.
  • SKU, DWU, tags, and region explain cost ownership, capacity expectations, and possible client-latency issues.
  • SQL script output shows which artifacts are published and whether deployment automation changed the expected query text.
  • Missing direct serverless pool lifecycle output is normal; validate serverless through workspace, scripts, objects, and query behavior.

Mapped commands

Dedicated SQL pool lifecycle

direct 
az synapse sql pool list --workspace-name <workspace-name> --resource-group <resource-group>
az synapse sql pooldiscoverAnalytics
read-only Microsoft Learn 
az synapse sql pool show --workspace-name <workspace-name> --resource-group <resource-group> --name <sql-pool-name>
az synapse sql pooldiscoverAnalytics
read-only Microsoft Learn 
az synapse sql pool pause --workspace-name <workspace-name> --resource-group <resource-group> --name <sql-pool-name>
az synapse sql pooloperateAnalytics
mutating Microsoft Learn 
az synapse sql pool resume --workspace-name <workspace-name> --resource-group <resource-group> --name <sql-pool-name>
az synapse sql pooloperateAnalytics
mutating Microsoft Learn 
az synapse sql pool wait --workspace-name <workspace-name> --resource-group <resource-group> --name <sql-pool-name> --updated
az synapse sql pooloperateAnalytics
read-only Microsoft Learn

SQL script evidence

supporting 
az synapse sql-script list --workspace-name <workspace-name>
az synapse sql-scriptdiscoverAnalytics
read-only Microsoft Learn 
az synapse sql-script show --workspace-name <workspace-name> --name <script-name>
az synapse sql-scriptdiscoverAnalytics
read-only Microsoft Learn

Architecture context

Architecturally, Synapse SQL is not one magic database engine. It is a decision point between lake-first querying and provisioned warehouse execution. Serverless SQL belongs near discovery, lightweight transformation, external table access, lake database querying, and BI over files when data volumes and scan patterns are understood. Dedicated SQL pool belongs where distribution design, workload groups, materialized views, statistics, and predictable concurrency justify provisioned capacity. Both models should be designed with the workspace managed identity, storage region, private endpoints, SQL authentication, Microsoft Entra access, diagnostic logs, and client tools. A mature design names which workloads use serverless, which use dedicated, and how data moves between them. This prevents teams from using one SQL label for very different operating models.

Security
Synapse SQL security spans SQL permissions, Synapse RBAC, Azure RBAC, storage access, credentials, firewall rules, private endpoints, and auditing. Serverless queries may expose lake files if external data sources, scoped credentials, or storage ACLs are too broad. Dedicated SQL pool brings table-level permissions, workload isolation, and administrator controls, but it still depends on network and identity boundaries. Operators should separate development and production scripts, avoid shared SQL admin secrets, and prefer Microsoft Entra authentication where possible. Sensitive external tables need reviewed data source paths and managed identity permissions. Security reviews should ask who can run queries, who can create metadata objects, and what data those objects can reach. Review public network access whenever new reporting tools are onboarded.
Cost
Synapse SQL cost follows the execution model. Serverless SQL is charged by data processed, so careless SELECT statements over broad folders, unpartitioned data, or inefficient formats can create surprising bills. Dedicated SQL pool cost comes from provisioned capacity and storage, so forgetting to pause nonproduction pools or oversizing DWU wastes money even when few users query it. External tables, result exports, diagnostics, and data movement can add indirect storage and logging cost. FinOps reviews should connect query patterns, file layout, BI refresh frequency, pool pause schedules, and owner tags. The right cost decision may be partitioning files, materializing a table, shrinking a pool, or moving a workload between models. Budget alerts should distinguish exploratory spikes from planned warehouse runtime.
Reliability
Reliability in Synapse SQL depends on the chosen model. Serverless SQL can be operationally simple because there is no dedicated cluster to patch or pause, but queries still fail when files move, schemas drift, credentials expire, or storage throttles. Dedicated SQL pool gives more predictable warehouse control, yet it can be paused, scaled, blocked by bad statistics, or slowed by skewed distribution. Operators should validate pool state, SQL script version, external object dependencies, data arrival, and monitoring signals before declaring a database outage. Reliable designs include schema contracts for lake files, retry-aware pipelines, workload isolation for critical reports, and a tested resume path for dedicated pools. Capacity and endpoint checks should be part of every critical dashboard runbook.
Performance
Synapse SQL performance depends on data layout, model choice, query shape, statistics, distribution, file format, region proximity, and concurrency. Serverless SQL performs best when files are columnar, partitioned, and filtered so less data is scanned. Dedicated SQL pool performance depends heavily on distribution columns, materialized views, table statistics, resource classes, and DWU capacity. Operators should not tune by guesswork. They should compare query duration, bytes scanned, data movement, skew, wait times, and BI refresh behavior. A serverless query that scans every Parquet file may need partition pruning. A dedicated query with massive shuffle may need distribution redesign. The term is broad, but the evidence must be specific. Review client retry behavior because BI tools can multiply a single slow query.
Operations
Operating Synapse SQL means treating SQL code, metadata, capacity, and access as controlled assets. Teams inspect dedicated pool state, SKU, pause or resume status, workload management settings, query history, failures, and storage dependencies. They also review serverless SQL scripts, external tables, credentials, and file paths because those define what data gets queried. Azure Monitor, Synapse Studio, CLI exports, and source control should agree on the current design. Changes need runbooks: scale dedicated capacity before heavy loads, pause after batch windows, validate external schemas after upstream changes, and capture evidence for audit. Good operations also separate emergency access from normal development privileges. Keep owner, refresh, and escalation details with each production SQL asset.

Common mistakes

  • Saying Synapse SQL without clarifying whether the workload is serverless, dedicated, or a mix of both.
  • Using serverless SQL for repeated BI scans without partitioning, views, or cost guardrails.
  • Leaving dedicated SQL pools running overnight after test or development workloads finish.
  • Blaming SQL capacity when the real issue is a missing lake file, changed schema, or storage permission.
  • Assuming every T-SQL feature behaves the same across dedicated and serverless consumption models.