The SQL in a case study is no harder than the rest of the exam. What's added is requirements traceability — pulling a dozen requirements out of prose, holding them all at once, and checking every option against all of them. That's a trainable skill, and it's the larger-scale version of the one thing that has cost you marks all along.
A case study is a tabbed scenario document with a set of questions attached. The scenario stays on screen the whole time — you can flip between tabs on every question. The usual tabs:
| Tab | Contains | Read priority |
|---|---|---|
| Overview | The company, what it does, where it operates | Last, or never — but scan it for the platform name |
| Existing environment | Current databases, applications, versions, problems | Second |
| Business requirements | What the business needs, in business language | First |
| Technical requirements | Constraints, SLAs, platform rules | First |
| Exhibits (sometimes) | Table DDL, a T-SQL fragment, a config file | Only what a question points at |
| You can | You cannot |
|---|---|
| Move freely between questions inside the case study section | Return to the section once you leave it |
| Mark questions for review within the section | See the case study again from the final review screen |
| Re-read any tab at any time while in the section | Go back to questions you'd already seen if you take a break mid-case-study |
Not the Overview. The Overview is scene-setting written to consume your time — with one exception noted in step 2. Go straight to Business requirements and Technical requirements; that is where every answer is decided.
Somewhere in the background prose there is usually a sentence naming the platform — "Azure SQL Database", "SQL Managed Instance", "SQL Server on a VM", "Microsoft Fabric". That single phrase silently eliminates whole families of options (multiple filegroups, filegroup backup, cross-database queries, Agent jobs). Find it, note it, then leave the tab.
On your whiteboard, write each one as a short tag. Three to five words each — you're building an index, not a transcript.
B1 archive month <1 min T1 date-range partition elimination
B2 supplier sees own only T2 globally unique order line
B3 mask phone, usability only T3 one request, no backend
B4 DBA cannot read NI T4 600k articles, <1 sec
B5 answer only from articles T5 validate before merge
B6 no API keys anywhere T6 diagnose via QPI
T7 PRIMARY filegroup onlyThis takes about three minutes and pays for itself by question two. You'll reference the list nine times.
Usually two or three — and here's the part people miss: at least one of them is normally not mentioned in the question at all. The question asks about the primary key; the requirement that decides it is the archiving one three bullets away.
Not just the one the question names. When two options both look right, the tiebreak is always a requirement one of them quietly violates.
Lakeside Outdoors is a United Kingdom retailer of outdoor and camping equipment. The company operates 240 physical stores and an e-commerce storefront, and sells through a network of 1,800 independent suppliers.
Lakeside Outdoors is consolidating its data estate onto Azure SQL Database. The company is also launching an AI-powered product assistant for customers and a self-service portal for suppliers.
Lakeside Outdoors plans to modernise its order history retention process, launch the supplier portal, deploy the AI product assistant, and formalise its database deployment pipeline.
SalesDB contains Sales.Order, Sales.OrderLine and dbo.Customer.
CatalogDB contains dbo.Product (1.4 million rows), dbo.Supplier and dbo.SupportArticle (600,000 articles averaging 3,000 words each).
Sales.OrderLine contains 2.1 billion rows and grows by approximately 140 million rows per month. A nightly job deletes rows older than 36 months in batches. The job currently runs for five hours, causes blocking, and generates substantial transaction log activity.
The storefront issues many short SELECT statements per page request. During peak periods, CPU utilisation and Data I/O utilisation both remain below 20%, but page latency exceeds two seconds.
dbo.Customer contains the columns NationalInsuranceNumber, DateOfBirth and PhoneNumber. PhoneNumber is not classified as sensitive and is not encrypted.
The storefront is a .NET application hosted in Azure Container Apps.
The supplier portal will be a React single-page application. No server-side application code will be written for it.
The product assistant has not yet been built.
An Azure AI Foundry resource named foundry-lakeside hosts a text-embedding-3-small deployment and a gpt-4.1-mini deployment.
All reporting queries against Sales.OrderLine filter on OrderDate.
PhoneNumber on screen. This is a usability measure and is not a compliance control.NationalInsuranceNumber in plaintext. The storefront must still be able to look up a customer by exact National Insurance number.OrderDate must read only the relevant months of data.CREATE TABLE Sales.OrderLine (
OrderLineID BIGINT IDENTITY(1,1) NOT NULL,
OrderID BIGINT NOT NULL,
OrderDate DATETIME2(0) NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL,
CONSTRAINT PK_OrderLine PRIMARY KEY CLUSTERED (OrderLineID)
);
CREATE NONCLUSTERED INDEX IX_OrderLine_Product
ON Sales.OrderLine (ProductID) INCLUDE (Quantity, UnitPrice);CREATE TABLE dbo.Product (
ProductID INT PRIMARY KEY,
SupplierID INT NOT NULL,
Name NVARCHAR(200) NOT NULL,
StockLevel INT NOT NULL
);RANGE LEFT with end-of-month boundary values, and a partition scheme mapping each partition to a separate filegroup.RANGE RIGHT with first-of-month boundary values, and a partition scheme mapping all partitions to [PRIMARY].RANGE RIGHT on OrderLineID, and a partition scheme mapping all partitions to [PRIMARY].DELETE and add a filtered index on OrderDate.B1T1T7 — three requirements, and the question names none of them.
Why B. Partitioning on OrderDate with first-of-month boundaries requires RANGE RIGHT, so the first instant of a month lands with that month. Partitioning on the date column is what delivers partition elimination for T1. Mapping everything to [PRIMARY] satisfies T7 — and is the only legal choice on Azure SQL Database anyway. SWITCH then retires a month as a metadata operation, satisfying B1.
A fails twice: RANGE LEFT with end-of-month boundaries misplaces rows from the last day of each month (on a datetime2 column, anything after midnight on the boundary date falls into the next partition), and multiple filegroups violate T7 and are impossible on this platform. C partitions on the wrong column — filtering by date wouldn't align with ID-based partitions, so T1 fails. D doesn't change the fundamental cost of deleting hundreds of millions of rows.
OrderLineID.PRIMARY KEY CLUSTERED (OrderLineID, OrderDate).OrderLineID to UNIQUEIDENTIFIER with a NEWID() default.T2B1 — and B1 is the one that decides it, despite not being mentioned.
Why C. A unique index on a partitioned table must include the partitioning column, so the key becomes (OrderLineID, OrderDate). IDENTITY still guarantees every generated OrderLineID is unique across the table, so T2 is satisfied by the column's semantics even though the constraint spans two columns.
OrderLineID alone and looks like a cleaner fit for T2. But a non-aligned index blocks partition SWITCH, which destroys B1 — a requirement the question never mentions. If you only checked the requirement named in the stem, you picked A.B abandons the key entirely. D introduces a random 16-byte clustering key on a 2.1-billion-row table with 140 million monthly inserts — severe fragmentation, and it doesn't solve the partitioning constraint either.
SupplierID.SupplierID, and block predicates AFTER INSERT and AFTER UPDATE.SELECT on the view.SupplierID.B2 — one requirement, but it contains three verbs.
Read B2 again: suppliers must "see only their own products" (→ filter predicate), must not "create a product for another supplier" (→ AFTER INSERT), and must not "reassign an existing product to another supplier" (→ AFTER UPDATE). Three controls, one requirement.
SupplierID — and then watch it vanish from their own view.C is bypassable by querying the base table, and 1,800 views is unmanageable. D is a display control, not a boundary — and masking an ID column doesn't restrict anything.
ENCRYPTBYKEY.B4 — a requirement with two clauses that must both hold.
Clause one: "administrators must never read it in plaintext" → Always Encrypted, because the keys live outside the database. Clause two: "look up a customer by exact National Insurance number" → deterministic, the only encryption type supporting equality search.
A decrypts transparently for anyone who can authenticate, DBAs included — TDE protects files, not queries. B satisfies the security clause but breaks the lookup. D is server-side, so any DBA with key access reads plaintext.
partial() function, and do not grant UNMASK to the support role.SELECT on the column to the support role.B3 — and the deciding phrase is the disclaimer at the end.
Everywhere else, DDM is the wrong answer because it's defeatable by inference. Here the requirement explicitly says "this is a usability measure and is not a compliance control" — the stem has deliberately lowered the bar, which is precisely the narrow case where masking is correct.
B filters rows, not columns. C hides the value entirely — agents need the last four digits. D is disproportionate for a column that isn’t classified as sensitive.
NationalInsuranceNumber, which can’t work. If a scenario asks you to mask an encrypted column, the scenario is the trap.CatalogDB directly.T3 — plus a constraint hidden in the Existing environment tab.
"In a single request" across a supplier, its products and their stock levels is exactly what GraphQL relationship traversal delivers. REST would need one call for the supplier and another for the products.
D would embed credentials in a browser application — indefensible, and it also conflicts with B6's spirit.
VECTOR_DISTANCE.AI_GENERATE_CHUNKS, embed each chunk, create a DiskANN vector index, and query with VECTOR_SEARCH.VECTOR_DISTANCE across all chunks.FREETEXT.T4 — two numbers in one requirement, each ruling out a different option.
"3,000 words per article" (from Existing environment) forces chunking — one vector for a 3,000-word article is a blurred average that matches nothing precisely. "600,000 articles, under one second" forces approximate search with a DiskANN index; exhaustive comparison across millions of chunks won't hit that latency.
A fails both halves. C chunks correctly but keeps the exhaustive scan, so it fails the latency requirement — this is the near-miss designed to catch a partial reading. D is keyword search: customers asking questions in their own words won't share vocabulary with the articles.
POST.foundry-lakeside.GET and pass the prompt as a query string parameter.B5B6
A delivers both halves of B5 — grounding in retrieved content, plus explicit permission to decline. Without that second clause a model asked to always answer will invent something. B eliminates the key entirely, which is what B6 demands.
D is mechanically impossible: GET has no request body, so a multi-kilobyte prompt has nowhere to go. E directly contradicts "using only content drawn from support articles".
Dropdown 1 — to satisfy T5, the pull request validation pipeline must run:
dotnet build B. dotnet publish C. sqlpackage /Action:Export D. sqlpackage /Action:ImportDropdown 2 — to satisfy T6, Query Performance Insight should be configured with:
T5T6 — and each dropdown scores separately, so partial credit is available.
Dropdown 1. PR validation happens before the merge, against no database. dotnet build compiles the project into a .dacpac and surfaces unresolved references and syntax errors without needing a connection — which is exactly why it's the right validation step. Publishing the dacpac (SqlPackage /Action:Publish) is the deployment step; T5's second clause ("the same artifact must then be deployed") describes what happens after the merge, using the artifact the build produced.
Dropdown 2. The Existing environment tab says CPU and Data I/O are below 20% while latency exceeds two seconds. Low resource usage plus high latency means the database isn't working hard — the problem is the number of round trips, not the cost of any query. That eliminates every resource-based and duration-based view. Only the Custom view lets you rank by Execution count.
Raw score matters less than which kind of mistake you made. Categorise each miss:
| If you missed… | Because | Fix |
|---|---|---|
| Q2, Q3, Q7 | You answered the requirement the question named and missed a second one | Step 5 of the method — check every tagged requirement, every time |
| Q1 | You didn't register "Azure SQL Database" | Step 2 — note the platform before anything else |
| Q5 | You applied "DDM is never right" as a rule | The stem outranks your memorised rules. Look for disclaimers. |
| Q6, Q9 | The deciding fact was in Existing environment, not Requirements | Read the environment tab too — constraints hide there |
| Q8 | You stopped reading B6 before "or a secret store" | Read requirement sentences to the full stop |
| Q4 | Two-clause requirement, satisfied one | Underline every "and" in a requirement |
| Source | Value |
|---|---|
| Caldera Energy, the second case study in this kit | Same collision design, different traps. Do it a few days after this one. |
| This one, re-timed in a week | You'll remember some answers — that's fine. Time the reading and tagging, not the answering. |
| Case studies from DP-300 or AZ-305 practice material | Wrong content, right format. Use them purely to drill requirement extraction. |
| Microsoft's official practice assessment | Worth checking whether it includes case studies at all — many practice assessments don't, which means the two you've taken may not have exercised this skill. |