SQL Server and Cosmos DB, side by side

Pinch or Ctrl+scroll to zoom; the text is selectable. Source · TheYard

SQL Server and Cosmos DB, side by side: the same application on Azure SQL Database (left) and Azure Cosmos DB (right), row by row SQL Server and Cosmos DB, side by side: one application, two stores, the same rows Read across. Each row is a question a developer who knows one of these and not the other asks first; each box names the file in this repository that answers it, and each number is one a record measured. Azure SQL Database The live site's default store: https://theyard.stevenstout.biz Azure Cosmos DB for NoSQL The second site's default store: https://theyard-cosmos.stevenstout.biz What it is the model each engine enforces A relational database Azure SQL Database, General Purpose serverless, the free limit (ADR-039) Tables, rows, foreign keys and T-SQL. The schema is the contract and the engine enforces it: a row that breaks a constraint is refused before it lands. The server sat one region from the compute when the numbers below were measured, which is the fact behind most of them; since 20 September 2026 the compute is in its region (ADR-079). A document database Azure Cosmos DB for NoSQL, account cosmos-theyard-ss, free tier (ADR-059) JSON documents in containers, each container split by a partition key. The document owns its shape; the engine enforces the key, the id and the etag, and nothing else. The account was in the compute's own region when the numbers below were measured, so a round trip was milliseconds; since 20 September 2026 it is one region away (ADR-079). How the application reaches it the adapter behind the same three ports Entity Framework Core over SqlClient api/TheYard.Infrastructure/YardConnection.cs, EfSources.cs, SqlLogInterceptor.cs A DbContext per request, LINQ that EF turns into statements, and a managed identity token instead of a password. The interceptor records every statement, parameters by name and never a value (ADR-043). The Cosmos DB SDK, directly api/TheYard.Infrastructure.Cosmos/CosmosStore.cs, CosmosSources.cs One CosmosClient for the process, point reads and queries by hand, so the request charge and the diagnostics stay visible. Managed identity with a data-plane role; local authentication is disabled, so no key exists to leak (ADR-062). A bid, at rest the same fact, stored two ways One row in dbo.Bids api/TheYard.Database/Tables/Bids.sql PRIMARY KEY (UserId, VehicleId). A FOREIGN KEY to AspNetUsers with ON DELETE CASCADE, so deleting an account takes its bids. A rowversion column the engine maintains, so a lost update is impossible to write by accident. One document in the bids container infra/cosmos/bids.json, partition key /user_id id is the vehicle, the partition is the buyer, so every write is a point write inside one partition. The etag is the concurrency token. No foreign key exists; the code keeps that rule, and the indexing policy excludes every path because nothing queries them. Writing a bid read, decide, write, and what happens when two writers race Find, then Add or update, then SaveChanges api/TheYard.Infrastructure/EfSources.cs, region bid-store Two statements. A DbUpdateConcurrencyException means another writer moved the row between the read and the write; the store starts again from what is there now, three tries, then the exception travels. measured from the container: 84 ms, 2 statements (ADR-067) Point read, then CreateItem or ReplaceItem with If-Match api/TheYard.Infrastructure.Cosmos/CosmosSources.cs, region cosmos-bid-store Two operations. A 412 is the same race as the concurrency exception and a 409 is a create that lost; the same three tries. The read costs 1 RU, the create 5.52, the replace 10.29. measured from the container: 10 ms, 2 operations, 6.52 RU (ADR-067) Keeping an address unique the guarantee one engine gives and the other has to be built A unique index does it api/TheYard.Database/Tables/Identity/AspNetUsers.sql, UserNameIndex The address is the user name here, and the unique index on NormalizedUserName refuses a second account with the same one. Nothing in the application has to remember to check. A claim document does it api/TheYard.Infrastructure.Cosmos/CosmosUserStore.cs, region create No unique index spans partitions. The store creates a document whose id is the address first; a 409 on that create is the refusal. If the account write then fails the claim is removed, and a process that dies between the two leaves a claim with no account, which is the written-down cost of the shape (ADR-061). Indexes, and what a query costs where the design work goes on each side Indexes chosen per query, in the DACPAC api/TheYard.Database (the SQL project), ADR-040 A clustered primary key, nonclustered indexes where a query needs them, and a plan the engine picks. The cost of a query is compute time and it shows up as vCore-seconds against the free limit. An indexing policy per container, in JSON infra/cosmos/*.json, ADR-058 Every path indexed by default, and here every path excluded on the four site containers because they are read by id. The cost of an operation is request units, charged per call, and the wrong partition key turns a 1 RU read into a fan-out across every partition. the experiment: 8.84 RU per seeded document tuned, 16.07 under the default (ADR-058) Consistency and transactions how far one write can reach ACID across tables one transaction, any number of tables, the engine arbitrates A transaction can touch every table, row versioning keeps readers off writers, and the database is the single arbiter of what happened in what order. Session consistency, atomic inside one partition TransactionalBatch, one partition key at a time A reset deletes one buyer's bids as one batch because they share a partition. Nothing spans partitions atomically; the application is arranged so nothing needs to. What the Admin tab shows the same page on both sites Every statement, with its time the SQL card and the console log, category Microsoft.EntityFrameworkCore.Database.Command The statement, its parameters by name, the milliseconds. On the SQL site the Timing card's SQL line is the ring these feed (ADR-043). Every operation, with its charge the operations card and the console log, category TheYard.Infrastructure.Cosmos.CosmosStore The operation, the container, the request units, the milliseconds and whether it named a partition or fanned out. Since 1.0.0.103 each is also one log line beside the SQL ones (ADR-062). Measured on this application twenty paired rounds from Missouri, and the proof from the container itself The relational side ADR-064 (the client), ADR-067 (the proof) From Missouri: bid write 219 ms, sign in 249 ms, register 319 ms at p50. From the container: 39 ms of every operation is the round trip to a server one region away. the proof: on 3 of 8 paths the same time; on the other 5 the difference is the round trip The document side ADR-064 (the client), ADR-067 (the proof) From Missouri: bid write 135 ms, sign in 221 ms, register 216 ms at p50. From the container: 2 ms round trip, and every sign-in costs exactly 2 RU. taking one round trip per operation off each side leaves them the same What it costs this month both stores at $0.00, read from the meters The free limit 100,000 vCore-seconds and 32 GB a month, renewing, ADR-039 Serverless compute that auto-pauses when nobody visits, so the first request after a quiet stretch waits for it to wake (a 25 s health check was measured once). The cost so far is $0.00. The free tier 1000 RU/s and 25 GB for the life of the account, ADR-059 Shared across the database's containers, never paused, and the site's whole month is pennies of it at serverless rates. The store costs $0.00; the site that serves it shares one $24.82 App Service plan with the other and the rendering service. The records behind the left column: ADR-039 The SQL Server backend, ADR-040 Data first, and the database in source control, ADR-041 Two providers and a SQL project, explained, ADR-043 What the database is actually doing. Behind the right: ADR-058 The partition key, ADR-059 A second store on Cosmos DB, and what it costs, ADR-060 The ports learn to wait, ADR-061 Accounts on a document store, ADR-062 What the store is actually doing. Across both: ADR-063 Backends, side by side; ADR-064 Measuring both stores; ADR-065 Cosmos DB, explained for someone who knows SQL Server; ADR-066 One container, both stores; ADR-067 Same performance, proven; ADR-069 A permanent address for the second site. Both sites open both stores; each site serves its default and the Store bar links to the other at the same page. A measurement can name the other store for one request with the X-Yard-Store header. Source: docs/images/sql-vs-cosmos.svg in the repository, drawn by docs/images/sql-vs-cosmos.mjs and redrawn when a store, a file or a number changes.