Azure SQL vs Cosmos DB: Relational vs NoSQL
Azure SQL Database and Cosmos DB are both fully managed Azure database services, but they solve fundamentally different problems. Azure SQL is a relational database with full SQL support, ACID transactions, and structured schema enforcement. Cosmos DB is a globally distributed NoSQL database optimised for low-latency access at any scale, with flexible schema and multiple consistency models. Choosing the wrong one creates technical debt that is expensive to undo.
The core data model difference
Azure SQL Database stores data in tables with defined columns and data types. Relationships between entities are expressed as foreign keys. Queries use standard T-SQL with full JOIN support, aggregations, window functions, and stored procedures. The schema is enforced — you cannot store an unexpected column without altering the table definition first.
Cosmos DB (NoSQL API) stores data as JSON documents within containers. A container is similar to a table but without a fixed schema — each document can have different fields. Documents are partitioned by a partition key you define. Relationships between entities are typically handled through denormalisation (embedding related data in the same document) rather than foreign keys.
This difference is fundamental: relational databases are optimised for complex queries with ad-hoc JOINs; document databases are optimised for fast reads and writes on known access patterns with documents pre-structured for the query.
Head-to-head comparison
| Dimension | Azure SQL Database | Cosmos DB (NoSQL API) |
|---|---|---|
| Data model | Tables, rows, columns — relational | JSON documents in containers — document |
| Query language | T-SQL — full JOINs, subqueries, CTEs, window functions | SQL-like syntax — no cross-document JOINs |
| Transactions | Full ACID across multiple tables and rows | ACID within a single logical partition; limited cross-partition |
| Consistency models | Strong consistency by default | 5 levels: Strong, Bounded Staleness, Session, Consistent Prefix, Eventual |
| Global distribution | Active geo-replication (up to 4 read replicas) | Multi-region write and read with configurable consistency |
| Schema flexibility | Fixed schema — ALTER TABLE required for changes | Schema-free — fields vary per document |
| Pricing model | vCore or DTU — fixed compute rate | Request Units/second (provisioned) or per-operation (serverless) |
| Read latency (single item) | 1–10ms (primary region) | Single-digit milliseconds guaranteed globally |
| Horizontal scaling | Limited — read scale-out, Elastic Pools, Hyperscale tier | Unlimited horizontal partitioning by design |
Pricing models compared
Azure SQL Database offers two compute models:
- vCore model: General Purpose tier, 4 vCores = approximately $368/month (East US). You pay for provisioned compute regardless of load. Serverless tier (auto-pause) pauses the database after idle period and charges per vCore-second when active — minimum cost approximately $15/month.
- DTU model: S3 Standard (100 DTUs) = approximately $75/month. Simpler pricing but less visibility into resource allocation.
Cosmos DB offers two billing models:
- Provisioned throughput: You allocate Request Units per second (RU/s). 100 RU/s costs $6/month in a single region. 1,000 RU/s = $60/month. Storage is $0.25/GB/month. Multi-region writes add a per-region multiplier to throughput costs.
- Serverless: Pay per operation — $0.25 per million RUs consumed. No provisioning required. Best for development, variable workloads, and workloads averaging under ~750,000 RU/s equivalent per month.
Cosmos DB can be significantly more expensive than Azure SQL for analytical query patterns (which consume many RUs per query) but significantly cheaper for high-volume, simple point reads at a consistent partition key.
Cosmos DB costs can spike dramatically if an application runs unoptimised queries (cross-partition queries, without filter on partition key) or if the partition key design causes “hot partitions” — one partition handling disproportionate traffic. Partition key design is the single most important architectural decision when using Cosmos DB.
When to choose Azure SQL
- The data is relational — entities have defined relationships, and queries need to JOIN across multiple entities frequently
- The application requires full ACID transactions across multiple tables (e.g., financial transactions, order management with inventory updates)
- Reporting and analytics are a first-class requirement — complex SQL aggregations, window functions, and ad-hoc queries
- The team is experienced with SQL and relational modelling — the operational complexity is well-understood
- Schema is stable and well-defined — changing schema occasionally is acceptable; evolving it constantly is not
- The data volume and query rate fit within the scale limits of a single Azure SQL instance or Elastic Pool
When to choose Cosmos DB
- The application needs single-digit millisecond reads globally — user profiles, product catalogues, gaming leaderboards
- The data volume or throughput exceeds what a single relational database can handle — millions of writes per second
- Global distribution is a first-class requirement — the application needs active writes in multiple Azure regions simultaneously
- The data structure is variable or evolving rapidly — IoT telemetry, event logs, user-generated content
- Access patterns are known and consistent — the document model can be designed for the application’s specific queries
- Workloads with very high read volume at a consistent partition key — session stores, cache backing stores, feature flags
Common mistakes
- Choosing Cosmos DB because it sounds modern, then using it like a relational database. Designing a Cosmos DB schema with many small documents that need to be JOINed in application code replicates the relational model poorly and at high cost. Cosmos DB requires a different data modelling approach.
- Poor partition key design in Cosmos DB. A partition key with low cardinality (e.g., status = “active”/“inactive”) creates hot partitions where one partition receives nearly all traffic and is throttled. Partition keys should distribute requests evenly — user ID, device ID, or a synthetic composite key.
- Using Azure SQL Serverless for a latency-sensitive production API without testing auto-pause behaviour. SQL Serverless pauses after the configured idle timeout and takes 30–60 seconds to resume. An application that receives infrequent requests and needs sub-100ms response time should not use auto-pause in production.
- Assuming Cosmos DB is always cheaper at scale. For analytical workloads with complex, cross-document queries, Cosmos DB RU consumption can be enormous. Azure Synapse Link for Cosmos DB provides a path to analytical queries without impacting operational throughput, but it adds architectural complexity.
Summary
- Azure SQL is a relational database with full T-SQL, ACID transactions across tables, and strong consistency — the right choice for structured, relational data with complex query patterns.
- Cosmos DB is a globally distributed NoSQL database with configurable consistency, schema-free documents, and guaranteed single-digit millisecond latency at any scale — the right choice for high-throughput, globally distributed, or schema-variable workloads.
- Cosmos DB pricing is based on Request Units (RU/s) — partition key design directly determines cost; poor design creates expensive hot partitions.
- Do not model relational data in Cosmos DB using document JOINs in application code — this is the most common and most expensive architectural mistake when adopting Cosmos DB.
Frequently asked questions
Can I query Cosmos DB with SQL?
Cosmos DB NoSQL (the core API) uses an SQL-like query syntax for document queries. However, it is not standard SQL — it does not support JOINs across documents, transactions spanning multiple documents outside a single logical partition, or the full SQL feature set. If you need full SQL compatibility with JOINs, foreign keys, and stored procedures, use Azure SQL Database.
What are Request Units (RUs) in Cosmos DB and how do I estimate them?
A Request Unit (RU) is a normalised throughput unit in Cosmos DB that represents the CPU, memory, and IOPS consumed by a database operation. A single 1 KB point read costs 1 RU. Writes cost approximately 5–6 RUs per 1 KB. You provision throughput in RU/s or use serverless billing. The Azure Cosmos DB capacity calculator helps estimate RU requirements from your expected request patterns.
Does Azure SQL Database support global replication?
Azure SQL supports active geo-replication (up to 4 readable secondary replicas in different regions) and auto-failover groups. However, read replicas in secondary regions require application-side connection string routing. Cosmos DB natively writes to and reads from any configured region transparently, making it a better fit for truly multi-region write scenarios.