Azure Synapse vs Azure SQL: Analytics vs OLTP

The core workload difference

OLTP (Azure SQL): An e-commerce platform processes thousands of order placements per minute. Each order inserts one row into the Orders table, reads a few rows from the Inventory table, and updates stock counts. These are small, fast operations touching a handful of rows. The database needs low latency (single-digit milliseconds), high concurrency (hundreds of simultaneous users), and strong consistency for transactions.

OLAP (Synapse): The same e-commerce platform’s finance team runs a monthly report: “Total revenue by product category, region, and customer segment for the past 12 months.” This query scans billions of rows across multiple tables, performs GROUP BY aggregations, and returns a summary of hundreds of rows. It runs for 5–30 minutes and is executed by 5 analysts, not 500 concurrent users.

These two patterns require opposite optimisations. Row-store indexes (used by Azure SQL) allow fast individual row lookups. Columnstore indexes (used by Synapse) compress and scan entire columns efficiently — exactly what aggregation queries need.

Head-to-head comparison

Pricing implications

Azure SQL General Purpose (East US): 4 vCores = ~$368/month (always-on). Serverless: pauses after idle timeout, charges per vCore-second when active — suitable for development or infrequently queried analytical databases.

Azure Synapse Dedicated SQL Pool: DW100c (minimum) = ~$1.51/hour = ~$1,093/month running 24/7. However, Synapse pools can be paused — during paused periods, you pay only for storage (~$23/TB/month), not compute. An analytical workload queried only during business hours (8 hours/day, 5 days/week) pays for approximately 160 hours/month of DWU compute instead of 730 hours: a 78% reduction in compute cost.

The pause-and-resume pattern is critical to Synapse cost management. A DW500c pool (~$7.55/hour) used 8 hours/day on weekdays costs approximately $1,208/month vs $5,481/month running continuously. This makes Synapse competitive for analytics workloads that do not need 24/7 availability.

Columnstore indexes in Azure SQL

Azure SQL includes columnstore index support, which blurs the line between OLTP and OLAP. A clustered columnstore index organises table data in column segments with heavy compression (typically 5–10x compression ratio). Analytical queries that scan large ranges of column data execute significantly faster with columnstore than with row-store indexes.

For “medium analytics” workloads — a database under 500 GB with BI queries running against current operational data — Azure SQL with columnstore indexes is often sufficient without the complexity and cost of a Synapse Dedicated Pool. The Azure SQL Hyperscale tier extends this to much larger databases while maintaining the full OLTP capability.

The limitation of Azure SQL columnstore for analytics is single-node computation. A query scanning 100 GB of data runs on a single Azure SQL node. Synapse distributes the same query across 60 compute nodes (at DW6000c), processing in parallel — fundamentally faster for very large scans.

Data loading patterns

Azure SQL accepts row-level inserts and updates efficiently. For bulk loading, BULK INSERT and the BCP utility load millions of rows per minute. Azure SQL is appropriate as the target for operational data in near-real-time ETL pipelines.

Synapse Dedicated Pool loads data most efficiently using COPY INTO from Azure Data Lake Storage Gen2 or Azure Blob Storage. The pattern is: land raw data in ADLS, transform with Azure Data Factory or Synapse Pipelines, load the transformed data into Synapse using COPY. Direct row-by-row inserts into Synapse are extremely slow — the columnstore format does not support efficient single-row operations.

-- Efficient bulk load into Synapse Dedicated Pool
COPY INTO dbo.FactSales
FROM 'https://mystorageaccount.blob.core.windows.net/data/sales/*.parquet'
WITH (
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Managed Identity')
);

When to choose each

Use Azure SQL when:

• The workload is OLTP — frequent individual row reads, writes, and updates
• Data volume is under 1 TB and analytics are secondary to transactional needs
• Hundreds of concurrent users or applications connect simultaneously
• Real-time data changes need to be immediately visible to queries

Use Synapse Dedicated SQL Pool when:

• Data volume exceeds 1 TB and analytical query performance is the primary requirement
• BI tools (Power BI, Tableau) connect directly and run complex aggregations
• Query concurrency is low (under 128 concurrent queries) and query duration is acceptable at minutes
• Batch ETL loading patterns are used rather than continuous row-by-row writes