SQL and EF Core Questions

Previously, in Part 5, you handled the web layer — middleware, DI lifetimes, REST design, and JWTs. Now we go where the interviewers who have been burned in production always go: the database. These four questions test whether you treat data access as an afterthought or as the place most performance stories begin.

"What does an index actually do, and what does it cost?"

Why they ask it: "Add an index" is the most common performance advice in software, and interviewers want to know whether you understand the machine behind the advice — or you're just repeating it. The word cost in the question is the real test.

A strong answer: Without an index, the database checks every row to answer your query — a scan. An index is a separate, sorted structure (a B-tree) that lets the engine jump almost directly to matching rows — a seek. Like the index at the back of a book: you don't read 400 pages to find one topic. Then the part most candidates skip: indexes are not free. Every INSERT, UPDATE, and DELETE must also update every index that touches the affected columns, so each index you add makes reads faster and writes a little slower — plus it takes disk space. That's a trade, and naming it as a trade is what earns the nod. We walk through the mechanics with pictures in our database indexes explainer.

Follow-ups to expect:

• "Clustered vs nonclustered?" — Clustered is the table's physical order (one per table); nonclustered is a separate structure pointing back at the rows.
• "Why not index every column?" — Write overhead and storage; the optimizer ignores unhelpful indexes anyway.
• "What's a covering index?" — One that contains every column the query needs, so the engine never touches the table itself.

"What is the N+1 problem and how do you fix it?"

Why they ask it: N+1 is the single most common performance bug in EF Core codebases, and it hides in code that looks perfectly innocent. Spotting it on sight is one of the clearest signals that you've worked on a real system.

A strong answer: N+1 is a loop of queries: one query to fetch a list, then one more query per item as you touch each one's related data. With 200 doctors, that's 201 round trips to the database. The code smell looks like this:

// Smells like N+1: one query for the doctors...
var doctors = context.Doctors.ToList();

foreach (var doctor in doctors)
{
    // ...then, with lazy loading, one MORE query per doctor
    Console.WriteLine($"{doctor.Name}: {doctor.Appointments.Count} appointments");
}

The fix is to tell EF up front what you need — either eager-load the relationship or, better for read-only screens, project exactly the columns you want:

// One query with a JOIN
var doctors = context.Doctors
    .Include(d => d.Appointments)
    .ToList();

// Or, for a report: ask only for what you actually need
var summary = context.Doctors
    .Select(d => new { d.Name, Count = d.Appointments.Count })
    .ToList();

Follow-ups to expect:

• "Include or projection — when each?" — Projection when you only read a few columns; Include when you need full tracked entities.
• "What does AsSplitQuery do?" — Splits one big JOIN into several queries to avoid duplicating parent rows across a wide result set.
• "How would you catch N+1 in production?" — Query logs, APM traces, or a spike in query count per request.

"Tracking vs no-tracking queries — when do you use each?"

Why they ask it: It's a cheap, quick probe of whether you know what EF Core does after the SQL returns — and whether you understand that convenience has a price.

A strong answer: By default, EF Core's change tracker takes a snapshot of every entity a query returns, so that SaveChanges can later diff the snapshot against your edits and write only what changed. That bookkeeping costs memory and CPU — and for a read-only screen it buys you nothing, because you never call SaveChanges. So the rule of thumb is simple: tracking when you intend to update, AsNoTracking when you only read.

// Read-only list for today's schedule — no tracking needed
var todaysAppointments = await context.Appointments
    .AsNoTracking()
    .Where(a => a.Date == today)
    .ToListAsync();

On list-heavy pages the difference is real, and you can even set no-tracking as the context-wide default and opt back in for writes.

Follow-ups to expect:

• "What happens if you edit a no-tracking entity and save?" — Nothing; the context has no idea the entity exists.
• "Can you change the default?" — Yes, QueryTrackingBehavior.NoTracking at the context level.
• "What is AsNoTrackingWithIdentityResolution?" — No tracking, but duplicate rows still resolve to one instance.

"A report query is suddenly slow in production but fast locally — walk me through your debugging."

Why they ask it: This is the senior-signal question of the data round. There is no single correct answer — the interviewer is grading your process. Do you measure before you change things, or do you guess?

A strong answer: Think out loud, in order. First, confirm where the time goes: is it the query or the app around it? Grab the actual SQL and its timing from logs. Second, compare the worlds: your local database has ten thousand rows and production has fifty million — a plan that scans happily at small scale falls over at real scale. Third, get the actual execution plan from production and look for scans where you expected seeks. Fourth, check statistics — if they're stale, the optimizer is estimating row counts from an old picture of the data and choosing badly. Mention parameter sniffing in one line: the engine may have cached a plan compiled for an unrepresentative parameter value and reused it for everything. Only after all that do you talk about fixes: an index, a rewrite, updated statistics. The order is the answer.

Bonus points for noting the tooling differs by engine — SQL Server's execution plans versus PostgreSQL's EXPLAIN ANALYZE. If you work across both, our SQL Server to PostgreSQL series maps one world onto the other.

Follow-ups to expect:

• "What is parameter sniffing?" — A cached plan optimized for the first parameter value, reused for values it fits badly.
• "How do stale statistics cause this?" — The optimizer's row estimates drift from reality, so it picks the wrong strategy.
• "What do you check before touching the query text?" — Plan, statistics, data volume, and blocking. Measure first.

Next up: the whiteboard

Web layer: done. Data layer: done. Now comes the round that worries junior developers most — and the one where, if you've followed this site's design series, you have a secret advantage. In Part 7, we walk through a full system design interview for a system you may have already designed: an appointment booking system for a clinic chain.