.NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .

NET Developer

MASTER THE .NET BACKEND &

SQL SERVER INTERVIEW

Stripping away vague theories and generic answers, this guide delivers 100+ highly
curated questions designed to test and elevate your engineering depth.

From the hidden memory overhead of boxing and unboxing to the architectural chess of the Outbox
and Strangler Fig patterns, this book bridges the gap between coding and high-performance system
design.

Whether you are aiming to ace your next technical round or searching for a bulletproof reference to
write cleaner, production-ready code—this guide is your ultimate backend blueprint.

MUHAMMAD AFZAL
(Full Stack .NET Engineer)

Contact

WhatsApp : 0092346 9888608

Email : afzalofficial911@[Link]

LinkedIn : [Link]

Confidential — For Interview Preparation Only 1 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

.NET BACKEND &

SQL SERVER
INTERVIEW PREPARATION GUIDE
100+ Curated Questions

Prepared By: Muhammad Afzal — Full Stack Developer

Stack: .NET Core | C# | SQL Server | REST APIs | Angular

🟢 Easy — Foundational 🟢 Medium — Applied 🟢 Hard — Expert/Tricky

📌 HOW TO USE THIS GUIDE: Work through each section in order. Time yourself — most interviews allow
3–5 mins per question. Focus on WHY, not just WHAT — interviewers reward deeper reasoning.

SECTION 1 — C# FUNDAMENTALS & OOP

▶ 1.1 Core C# Concepts

# Question Level
What is the difference between value types and reference types in C#? Give
Q1 Medium
memory-level explanation.

Ans In C#, value types store the actual data directly, while reference types store a reference (address)
pointing to where the data lives in memory.

🔹 Memory-Level Explanation
Value Type Reference Type
Stored in Stack Heap (reference on Stack)
Holds Actual value Memory address
Examples int, bool, struct, enum class, string, array, object
Copy behavior Copies the value Copies the reference

🔹 Real Example to Say

If I do int a = 5; int b = a; — changing b does not affect a, because they are two separate
copies on the stack.
But if I do var obj1 = new Person(); var obj2 = obj1; — both obj1 and obj2 point to the same
object on the heap. Changing one affects the other.

Confidential — For Interview Preparation Only 2 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

"One important thing — string in C# is a reference type, but it behaves like a value type
because of immutability. Every modification creates a new string object on the heap."

Explain boxing and unboxing. What are the performance implications and
Q2 Hard
when does it silently happen?
Ans Boxing is converting a value type to a reference type (object). Unboxing is the reverse — converting
it back to a value type.

🔹 Memory-Level Picture
Boxing:

The value 42 that lived on the Stack is now copied and wrapped into a new object on the Heap.

Unboxing:

The value is extracted from the Heap and copied back to the Stack.

🔹 Performance Implications
Problem Why it hurts
Heap allocation Every box creates a new object on heap
Garbage Collection pressure More heap objects = more GC work
Extra CPU cycles Copy + cast operations cost time
Slower in loops 1000 iterations = 1000 allocations
Boxing seems small but in a tight loop or high-traffic API, it can cause serious performance
degradation.

🔹 When Does It Silently Happen?

This is the part that impresses interviewers most — silent boxing:

1. Using non-generic collections

2. String formatting (older style)

3. Passing value type as interface

4. Using object as parameter

🔹 How to Avoid It

Confidential — For Interview Preparation Only 3 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

"Always prefer generic collections like List<T> or Dictionary<K,V> over non-generic ones specifically to
avoid hidden boxing overhead. It's a small habit that reflects performance-aware coding."

What is the difference between string and StringBuilder? When would you
Q3 Easy
choose one over the other?
Ans string is immutable — every change creates a new object in memory. StringBuilder is mutable — it
modifies the same object in place.

🔹 Memory-Level Picture
String — New object every time:

StringBuilder — Same object modified:

🔹 Side-by-Side Comparison
string StringBuilder
Mutability Immutable Mutable
Memory New object each change Same object modified
Performance Slow for many changes Fast for many changes
Thread safety ✅ Safe (immutable) ❌ Not thread-safe
Namespace System [Link]
Best for Few / no changes Many concatenations

🔹 When to Use Which?

✅ Use string when:

✅ Use StringBuilder when:

Confidential — For Interview Preparation Only 4 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

🔹 Real World Scenarios

Scenario Use
Building SQL query dynamically StringBuilder
Generating HTML/CSV in loop StringBuilder
Simple variable assignment string
Config values, names, labels string
Log message with 100+ appends StringBuilder
Displaying a user's name string

Explain the difference between == and .Equals() for strings and objects. Can
Q4 Medium
they give different results?
Ans == checks reference equality by default — are they the same object in memory? .Equals() checks
value equality — do they have the same content?

"But for strings, C# overrides == to behave like .Equals() — which is where it gets interesting."

🔹 For STRINGS — They behave the same

Why? Because C# overloads == for strings to compare content, not reference.

🔹 For OBJECTS — They CAN give different results

Both are different heap objects, so both return false here — unless you override .Equals() in your
class.

🔹 Where They DIFFER

Confidential — For Interview Preparation Only 5 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

When you cast string to object, == goes back to comparing references — and now they differ!

🔹 Quick Summary Table

Scenario == .Equals()
string literals ✅ Value compare ✅ Value compare
string cast to object ❌ Reference compare ✅ Value compare
Custom class (no override) ❌ Reference compare ❌ Reference compare
Custom class (with override) ❌ Reference compare ✅ Value compare
null check ✅ Safe ❌ Throws NullReferenceException

What are nullable value types (int?) and how does the compiler implement
Q5 Medium
them under the hood?
Ans By default, value types like int, bool, DateTime cannot be null — they always hold a value. int? is a
nullable value type that allows a value type to also represent null.

🔹 The Problem It Solves

This is extremely common when working with databases, APIs, or optional form fields where a value
may simply not exist.

🔹 Under The Hood — How Compiler Implements It

int? is NOT magic. It is simply syntactic sugar for Nullable<T> struct.

The Nullable<T> struct looks like this internally:

Confidential — For Interview Preparation Only 6 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

So int? is actually a struct with two fields — a boolean flag and the actual value. It still lives on the
Stack, not the Heap!

Explain the 'readonly' keyword vs 'const'. What are the key differences in
Q6 Medium
terms of compile-time vs run-time?
Ans const is a compile-time constant — value is fixed when code is compiled and never changes.
readonly is a runtime constant — value is set once, either at declaration or in the constructor, and
cannot change after that.

🔹 Side-by-Side Comparison First

const readonly
When set Compile time Runtime
Where set Declaration only Declaration or Constructor
Belongs to Class (always static) Instance or Class
Types allowed Primitives & string only Any type
Memory Inlined by compiler Stored in memory
Static by default ✅ Yes ❌ No

If the value is truly universal and will never change across any environment like Pi or
MaxPasswordLength, use const. But for anything environment-specific like connection strings, feature
flags, or startup timestamps, always use readonly — it's safer, more flexible, and avoids the cross-
assembly versioning trap.

What is the difference between 'is', 'as', and explicit casting? Which throws
Q7 Easy
and which returns null?
Ans is checks type without casting. as tries to cast and returns null if it fails. Explicit cast (Type) forces the
cast and throws an exception if it fails.

🔹 Quick Reference Table First

is as (Type) Explicit
Purpose Type check Safe cast Force cast
Returns bool Object or null Object or exception
On failure false null InvalidCastException
Works on Any type Reference & nullable only Any type
Null input false null Exception

🔹 Explicit Cast — Throws on Failure

Explicit cast says — I am 100% sure this is the right type. If you are wrong, runtime punishes you with
an exception.

🔹 as Operator — Returns Null on Failure

Confidential — For Interview Preparation Only 7 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

as is the safe version of explicit cast — it never throws, it just returns null if the cast is not possible.

🔹 is Operator — Just Checks Type

What is a delegate? How is it different from an interface? Explain Action,

Q8 Medium
Func, and Predicate.
Ans A delegate is a type-safe function pointer — it holds a reference to a method and lets you pass
methods as parameters, store them in variables, or call them later.

🔹 Real World Analogy

Think of a delegate like a job contract. The contract says — I need someone who accepts a string and
returns an int. Any method that matches that signature can fill the role.

🔹 Basic Delegate Example

The delegate variable can point to ANY method that matches its signature.

🔹 Delegate vs Interface — Key Difference

Delegate Interface
Represents A single method signature A contract with multiple methods

Confidential — For Interview Preparation Only 8 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

Best for Callbacks, events, passing methods Abstraction, polymorphism

Flexibility Any matching method, even anonymous Must implement full interface
Coupling Loose coupling Moderate coupling
Usage Events, LINQ, async Dependency injection, strategies
Use an interface when you need a full contract. Use a delegate when you just need to pass a single
behavior around.

🔹 Built-in Delegates — Action, Func, Predicate

Microsoft provides three built-in generic delegates so you never need to declare your own in most
cases.

🔸 1. Action — No Return Value

Use Action when your method does something but returns nothing (void).

🔸 2. Func — Has Return Value

Use Func when your method returns a value. Last type parameter is always the return type.

🔸 3. Predicate — Always Returns Bool

Use Predicate when you need to test a condition — always takes one input and returns bool.

Explain covariance and contravariance in generics (IEnumerable<out T> vs

Q9 Hard
IComparer<in T>).
Ans Covariance and contravariance are about type compatibility direction when working with
generics."
Covariance (out) means you can use a more derived type than specified — flows out of the
interface. Contravariance (in) means you can use a more base type than specified — flows
in to the interface.

🔹 The Problem Without Variance

Even though Dog is an Animal, List<Dog> is NOT a List<Animal> by default. Variance solves this.

🔹 Covariance — out T (Producer / Read-only)

Covariance says — if Dog is an Animal, then IEnumerable<Dog> can be treated as
IEnumerable<Animal>.

Why is out safe here?

 IEnumerable only PRODUCES values — you only READ from it

Confidential — For Interview Preparation Only 9 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

 You never INSERT an Animal back into dogs list

 So there is no risk of type corruption
out means the type only comes OUT of the interface — it is read-only, so it is safe to treat a derived
collection as a base collection.

🔹 Contravariance — in T (Consumer / Write-only)

Contravariance says — if Dog is an Animal, then IComparer<Animal> can be used where
IComparer<Dog> is expected.

Why is in safe here?

 IComparer only CONSUMES values — it takes values IN
 It never produces/returns a Dog or Animal
 An Animal comparer can safely compare Dogs
 because Dog IS an Animal — it has all Animal properties
in means the type only goes IN to the interface — it is consumed, never returned. So a more general
comparer can safely handle more specific types.

What is the 'dynamic' keyword and how does it differ from 'object'? When
Q10 Medium
not to use it?
Ans object is the base of all types — type checking happens at compile time. dynamic bypasses
compile-time checking entirely — all type resolution happens at runtime.

🔹 The Core Difference In One Shot

With object you must cast before using type-specific members. With dynamic the runtime figures it out
for you — no casting needed

🔹 Side-by-Side Comparison
object dynamic
Type checking Compile time Runtime

Confidential — For Interview Preparation Only 10 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

IntelliSense ✅ Available ❌ Not available

Casting needed ✅ Yes ❌ No
Performance Fast Slower (DLR overhead)
Error detection Compile time Runtime crash
Boxing ✅ Yes for value types ✅ Yes for value types
Inherits from Nothing (IS the base) object at runtime

🔹 When NOT To Use dynamic ❌

▶ 1.2 OOP Principles & Design

# Question Level
Q11 Explain the SOLID principles with a real-world .NET example for each. Hard

Ans SOLID is a set of 5 design principles that make code more maintainable, scalable, and testable. Each
principle solves a specific design problem that causes pain as software grows.

🔹 S — Single Responsibility Principle

A class should have only ONE reason to change.

❌ Violation — doing too many things

Confidential — For Interview Preparation Only 11 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

This class has 4 reasons to change — validation rules, database, email provider, logging format.
Change any one and you touch this class.

✅ Fix — one class, one responsibility

Confidential — For Interview Preparation Only 12 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

🔹 O — Open/Closed Principle
Open for extension, closed for modification.

❌ Violation — modifying existing code every time

✅ Fix — extend without touching existing code

Adding a new payment method means adding a new class — nothing existing is touched or risked.

Confidential — For Interview Preparation Only 13 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

🔹 L — Liskov Substitution Principle

Derived classes must be substitutable for their base class.

❌ Violation — subclass breaks parent behavior

✅ Fix — proper abstraction

Confidential — For Interview Preparation Only 14 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Every subclass must honor the contract of the base — no surprises, no broken expectations.

🔹 I — Interface Segregation Principle

Don't force classes to implement methods they don't need.

❌ Violation — fat interface

Confidential — For Interview Preparation Only 15 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

✅ Fix — small focused interfaces

Each class only depends on what it actually uses — no dead code, no forced implementations.

🔹 D — Dependency Inversion Principle

Depend on abstractions, not concrete implementations.

❌ Violation — tightly coupled to concrete class

✅ Fix — depend on abstractions

Confidential — For Interview Preparation Only 16 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

The controller doesn't care if storage is SQL or Mongo, or if AI is OpenAI or Gemini — it just uses the
abstraction. Switching providers requires zero changes to business logic.

🔹 All 5 Principles — Quick Reference

Principle Core Idea Violation Sign
S — Single Responsibility One class, one job Class changes for multiple reasons
O — Open/Closed Extend don't modify Adding features breaks existing code
L — Liskov Substitution Subclass = safe Subclass throws or breaks parent
replacement behavior
I — Interface Segregation Small focused interfaces Classes implement unused methods
D — Dependency Depend on abstractions new ConcreteClass() inside business
Inversion logic

What is the difference between abstract class and interface? When would
Q12 Medium
you choose each in C# ?
Ans An abstract class is a partially implemented base class — it can have both implemented and
unimplemented members. An interface is a pure contract — it defines what a class must do, but not
how.

Confidential — For Interview Preparation Only 17 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

🔹 Side-by-Side Comparison First

Abstract Class Interface
Implementation Can have full/partial Only contract (C# 8+ allows
implementation default)
Fields ✅ Yes ❌ No
Constructors ✅ Yes ❌ No
Access modifiers ✅ Any ✅ Public by default
Multiple ❌ One only ✅ Multiple allowed
inheritance
State ✅ Can hold state ❌ Cannot hold state
Best for Shared base behavior Capability contract

🔹 When To Choose Each

✅ Use Abstract Class when:

✅ Use Interface when:

Explain method hiding (new keyword) vs method overriding (override).

Q13 Hard
Why is hiding considered dangerous?

Confidential — For Interview Preparation Only 18 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Ans What is Overriding?
When we mark a method virtual in parent and override in child, the runtime decides which method to
call based on the actual object type. This is true polymorphism.

What is Hiding?
When we use the new keyword in child class, we are not overriding — we are hiding the parent method.
Now the compiler decides based on the variable type, not the actual object.

Why is Hiding Dangerous?

The danger is silent wrong behavior. Same object gives different results depending on how it's
declared. No exception, no warning — just unexpected output. It breaks polymorphism silently.

What is the diamond problem in multiple inheritance? How does C# handle

Q14 Hard
it with interfaces having default implementations?
Ans Diamond Problem:
The diamond problem happens when a class inherits from two parents that both inherit from the same
grandparent. Now the class has two copies of the same method — and the compiler doesn't know
which one to call.

How C# Avoids It?

C# simply doesn't allow multiple class inheritance. You can only extend one class. This
completely eliminates the diamond problem for classes.

But What About Interfaces With Default Methods? (C# 8+)

This is where it gets interesting. C# 8 introduced default interface methods — and now the diamond
problem can appear with interfaces.

The compiler refuses to compile and forces you to explicitly resolve the conflict yourself.

How to Resolve It?

You override the method in your class and explicitly call whichever interface version you want.

Confidential — For Interview Preparation Only 19 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Describe the difference between composition and inheritance. Why is
Q15 Medium
'favour composition over inheritance' good advice?
Ans Inheritance:
Inheritance is an IS-A relationship. Dog extends Animal — Dog gets all Animal behavior automatically.
It sounds convenient but it creates tight coupling between parent and child.

Composition:
Composition is a HAS-A relationship. Instead of extending a class, you inject the behavior you need as
a dependency. The class owns the capability without being locked into a hierarchy.

Why is Inheritance Dangerous Long Term?

When you change the parent class, every child is affected — even ones that didn't need that change.
This is called the fragile base class problem. Your hierarchy becomes a house of cards.

Why Favour Composition?

With composition you plug in exactly what you need, swap implementations easily, mock dependencies
in tests, and never worry about breaking a hierarchy. It directly supports the Dependency Inversion
Principle.

When is Inheritance Still Valid?

When there is a genuine IS-A relationship that will never change — like AdminUser extends User, or
SqlRepository extends BaseRepository. But even then I keep hierarchies shallow — maximum two
levels.

What design patterns have you used in production? Explain Repository,

Q16 Hard
Factory, and Decorator with practical use cases.
Ans Repository Pattern
"Separates data access logic from business logic"
Instead of writing database queries directly in my services, I create a repository layer that handles all
data operations. My business logic never knows whether data comes from SQL, MongoDB, or
anywhere else.

Confidential — For Interview Preparation Only 20 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Tomorrow if you switch from SQL to MongoDB — You only write a new repository class.

Factory Pattern
"Creates objects without exposing creation logic"
When object creation is complex or depends on conditions, I use a factory to centralize that
decision. The caller just says what it wants — not how to build it.

Confidential — For Interview Preparation Only 21 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Decorator Pattern
"Adds behavior to an object without changing its code"
When I need to add cross cutting concerns like logging, caching, or retry logic — I wrap the
original service in a decorator. Original class stays untouched.

Confidential — For Interview Preparation Only 22 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

The ChatService never knows caching exists — it just calls IAIProvider. I can stack decorators
— add logging decorator on top of caching decorator — without touching any existing code.

What is the Liskov Substitution Principle? Give an example of a violation

Q17 Hard
and how to fix it.
Ans LSP says — anywhere you use a base class, you should be able to substitute a derived class without
breaking the program's behavior. If your subclass breaks expectations of the parent, you are
violating LSP.

The Classic Violation — Rectangle & Square

A Square IS-A Rectangle mathematically — but in code it causes problems.

Confidential — For Interview Preparation Only 23 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

No exception thrown — just silently wrong behavior. This is the most dangerous kind of bug.

The Fix — Proper Abstraction

The real problem is forcing Square into Rectangle's hierarchy. They should both derive from a common
abstraction instead.

Confidential — For Interview Preparation Only 24 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Follow a simple LSP check — if a subclass throws NotImplementedException or

NotSupportedException on an inherited method, that is an immediate red flag. It means the inheritance
hierarchy is wrong and needs redesigning.

Explain the Strategy pattern vs the Template Method pattern. When do you
Q18 Hard
pick one over the other?
Ans Strategy Pattern
"Change the behavior by swapping the algorithm at runtime"
Strategy pattern defines a family of algorithms behind an interface and lets you switch between them at
runtime. The class delegates the behavior to whichever strategy is injected.

Confidential — For Interview Preparation Only 25 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Template Method Pattern

"Fix the skeleton, let subclasses fill in the steps"
Template Method defines the overall algorithm structure in a base class and lets subclasses override
specific steps — but the order and flow never changes.

Confidential — For Interview Preparation Only 26 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Key Difference — One Line

Strategy changes what algorithm runs at runtime through composition. Template Method changes
how steps are implemented at compile time through inheritance.

▶ 1.3 Memory, GC & Performance

# Question Level
How does the .NET Garbage Collector work? Explain generations 0, 1, 2
Q19 Hard
and the LOH.
Ans The Garbage Collector automatically manages memory in .NET. Instead of manually freeing memory
like in C++, the GC periodically finds objects that are no longer referenced and reclaims their memory.

How GC Decides What To Collect

Confidential — For Interview Preparation Only 27 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
GC starts from root references — static variables, local variables, CPU registers — and traces every
object reachable from them. Anything not reachable is considered dead and gets collected.

Why Generations Exist

Scanning the entire heap every time is expensive. Microsoft research showed that most objects die
young — a temporary string, a loop variable, a short lived request object. So GC divides heap into
generations and scans young objects more frequently.

Generation 0 — Newborns
Every new object starts in Gen 0. GC collects Gen 0 most frequently — hundreds of times per day. It is
small, fast, and most objects die here without ever promoting.

Generation 1 — Survivors
Objects that survive a Gen 0 collection get promoted to Gen 1. It acts as a buffer between short lived
and long lived objects. Collected less frequently than Gen 0.

Generation 2 — Long Lived

Objects surviving Gen 1 move to Gen 2. These are long lived objects — static data, caches, application
level services. Gen 2 collection is expensive and happens rarely.

LOH — Large Object Heap

Any object 85KB or larger goes directly to the Large Object Heap — bypassing Gen 0 entirely. LOH is
only collected during Gen 2 collections and is never compacted by default — meaning it can get
fragmented over time.

What is IDisposable and the Dispose pattern? What happens if you don't
Q20 Medium
call Dispose?
Ans IDisposable is an interface with a single Dispose() method. It exists for objects that hold unmanaged
resources — database connections, file handles, HTTP clients, streams — things the Garbage
Collector cannot clean up automatically.

Why GC Is Not Enough Here

GC only manages managed memory. A database connection, file handle, or socket is an operating
system resource — GC has no idea it exists. If you never release it, it stays open until the process
dies.

Full Dispose Pattern — With Finalizer Safety Net

Confidential — For Interview Preparation Only 28 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

The finalizer is a safety net — if a developer forgets to call Dispose, GC eventually calls the finalizer.
But finalizers are non-deterministic — you never know when GC will run them. That is why using is
always preferred.

What Happens If You Don't Call Dispose?

The unmanaged resource stays open. Database connections never return to the pool — pool gets
exhausted and new requests start failing. Files stay locked — other processes cannot access them.
Memory leaks grow over time. In a high traffic API this becomes a production incident very quickly.

Explain the 'using' statement. How does the compiler transform it? Does it
Q21 Easy
call Dispose on exception?
Ans The compiler transforms using into a try/finally block under the hood. Finally block always runs —
even on exception — which guarantees disposal.

Confidential — For Interview Preparation Only 29 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Does It Call Dispose On Exception?

Yes — absolutely. The finally block runs regardless. This is the entire point of using — guaranteed
cleanup no matter what happens.

In the modern using var syntax, if you have multiple disposable objects in one method, they
are disposed in reverse order of declaration when the method ends. Just like a stack — last in
first out. This matters when objects depend on each other, like a SqlCommand depending on
a SqlConnection.

What is a memory leak in .NET? Can managed code have memory leaks?
Q22 Hard
Give two common causes.
Ans A memory leak in .NET is when objects are no longer needed but GC cannot collect them because
something still holds a reference. Managed code absolutely can have memory leaks — GC only
collects what is unreachable, not what is unused.

The Key Misconception

Many developers think — I am using C#, GC handles memory, I cannot have leaks. That is wrong. GC
is not magic. If your code keeps a reference to an object alive unintentionally, GC will never touch it —
and memory grows forever.

Cause 1 — Static Collections Growing Forever

Static fields live for the entire application lifetime. If you keep adding objects to a static collection and
never remove them, those objects can never be collected.

Confidential — For Interview Preparation Only 30 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Cause 2 — Event Handlers Never Unsubscribed

This is the most common and sneaky memory leak in .NET. When object A subscribes to an event on
object B, object B holds a reference back to object A. Even if you think A is done, GC cannot collect it
because B still references it.

Confidential — For Interview Preparation Only 31 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

What is the difference between Span<T> and Memory<T>? Why were they
Q23 Hard
introduced?
Ans Span<T> — Stack Only, Ultra Fast
Span is a ref struct — it can only live on the stack. It is the fastest way to slice and work with
contiguous memory — arrays, strings, stack allocated memory — without copying anything.

Memory<T> — Heap Safe, Async Friendly

Memory is the async compatible version of Span. It can live on the heap, be stored in fields, and
safely cross async boundaries. When you actually need to process the data, you call .Span on it.

Why Were They Introduced?

Before Span and Memory, working with slices of arrays or strings always meant allocating a new
object on the heap. For high performance scenarios like parsers, network buffers, or file processing —
this constant allocation puts pressure on GC. Span and Memory were introduced to work with slices of
memory without any allocation.

What is the difference between 'struct' and 'class' beyond stack vs heap?
Q24 Medium
When would you make a struct?

Confidential — For Interview Preparation Only 32 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Ans The common answer is struct lives on stack, class lives on heap. But the real difference goes deeper —
it is about value semantics vs reference semantics, copy behavior, identity, and mutability.

Value Semantics vs Reference Semantics

When you copy a struct, you get a completely independent copy. When you copy a class, both
variables point to the same object. Changing one affects the other.

When Would You Make a Struct?

I follow Microsoft's guidance — make a struct only when ALL of these are true.

✅ Good struct candidate

1. Small — ideally under 16 bytes
2. Immutable — value never changes after creation
3. Short lived — not stored long term
4. Logically a single value — like a coordinate or money amount

Use structs for things like GPS coordinates and date ranges — small, immutable, frequently created
values where avoiding heap allocation matters. But for anything with behavior, identity, or complex state
— it is always a class.

Confidential — For Interview Preparation Only 33 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

SECTION 2 — ASYNC / AWAIT & THREADING

▶ 2.1 Async / Await Deep Dive

# Question Level
What does 'async/await' actually do? Explain the state machine the
Q25 Hard
compiler generates.
Ans async/await does NOT create a new thread. It is a compiler transformation that allows a method to
pause and resume without blocking the calling thread. While waiting for I/O — database, HTTP, file —
the thread is released back to the thread pool to serve other requests.

The Core Problem It Solves

Before async/await, waiting for a database call meant the thread sat idle — doing nothing, just waiting.
In a web API under load, all threads get exhausted and new requests start queuing. Async frees the
thread during the wait.

What Compiler Actually Generates — State Machine

The compiler transforms every async method into a state machine struct. It tracks where the method
paused and what to do when it resumes.

Confidential — For Interview Preparation Only 34 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Every await becomes a state number. MoveNext runs between states. Thread is only used when
actual code runs — not during the waiting.

What is the difference between [Link](), [Link](), and

Q26 Medium
async methods?
Ans All three involve tasks but they serve completely different purposes. async methods are for I/O
bound work. [Link] is for CPU bound work. [Link] is the old, dangerous, low level
version of [Link] with more control — but more risk.

async Method — I/O Bound Work

Async methods do NOT use a thread pool thread while waiting. The thread is released during await.
Perfect for database calls, HTTP requests, file reads — anything where you are waiting for external
work.

If you wrap this in [Link] — you waste a thread pool thread just to sit and wait. That is the wrong
tool.

Confidential — For Interview Preparation Only 35 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
[Link] — CPU Bound Work
[Link] pushes heavy CPU work to a thread pool thread so it does not block the calling thread —
typically the UI thread or request thread.

In an [Link] API, [Link] rarely makes sense — the calling thread is already a pool thread.
[Link] just borrows another pool thread — no real benefit, just overhead.

[Link] — Low Level, Handle With Care

StartNew is the old API from .NET 4. It gives more control — custom scheduler, cancellation, long
running hints — but has dangerous default behaviors that catch developers off guard.

[Link] is essentially a safe wrapper around [Link] with sensible defaults.

Side By Side Comparison

async/await [Link] [Link]
Best for I/O bound CPU bound Long running / advanced
Thread used Released during wait Pool thread Pool or dedicated thread
Async unwrapping ✅ Natural ✅ Correct ❌ Dangerous default
Modern recommendation ✅ Always ✅ CPU work ⚠️ LongRunning only

What is 'async void'? Why is it considered dangerous and when is it the

Q27 Hard
only valid choice?

Confidential — For Interview Preparation Only 36 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Ans async void is an async method that returns nothing. It is considered dangerous because
exceptions cannot be caught, the caller cannot await it, and failures happen silently —
often crashing the entire application.

Problem 1 — Exceptions Cannot Be Caught

When an exception is thrown inside async void, it is raised on the SynchronizationContext — not
back to the caller. Your try/catch is completely useless.

Problem 2 — Caller Cannot Await It

Because async void returns nothing, the caller has no Task to await. It cannot know when the method
finished or if it succeeded.

Problem 3 — Can Crash Entire Application

Unhandled exceptions from async void are thrown on the thread pool. In [Link] Core this crashes
the entire process — not just the request.

When Is async void The ONLY Valid Choice?

The only legitimate use case is event handlers. Event handler signatures are fixed by the framework
— they must return void. You cannot change that signature.

Explain ConfigureAwait(false). When should you use it and why does it

Q28 Hard
matter in [Link] Core vs classic [Link]?
Ans After await completes, by default code resumes on the original synchronization context.
ConfigureAwait(false) says — resume on any thread pool thread, skip the context switch.

When should you use it

If you write a reusable library or NuGet package — always use ConfigureAwait(false). Your library
might be consumed by classic [Link] or WinForms which DO have contexts.

Classic [Link] — Mattered A Lot

Classic [Link] had one thread per request context. Blocking on async caused deadlocks because
both threads waited for each other.

Confidential — For Interview Preparation Only 37 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
[Link] Core — No Longer A Deadlock Issue
[Link] Core removed SynchronizationContext entirely. Deadlocks of that type cannot happen.
ConfigureAwait(false) is optional in application code.

What is a deadlock in async code? Give a classic example and explain

Q29 Hard
exactly why it deadlocks.
Ans A deadlock happens when two pieces of code are waiting for each other — neither can proceed. In
async code it happens when you block on async code synchronously.

Why Exactly It Deadlocks — Step By Step

Thread 1 calls .Result — grabs the request context and blocks waiting for the Task. Task completes
but needs the same context to resume. Context is held by blocked Thread 1. Thread 1 waits for Task.
Task waits for Thread 1. Neither moves — deadlock.

Three Fixes

What is the difference between Task<T> and ValueTask<T>? When should

Q30 Hard
you prefer ValueTask?
Ans Task<T> always allocates a new object on the heap. ValueTask<T> is a struct — when the result is
already available synchronously, zero allocation happens.

The Problem Task<T> Has

Confidential — For Interview Preparation Only 38 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Even when result is immediately available, Task<T> still creates a heap object. Under high load —
thousands of unnecessary allocations per second.

ValueTask<T> — Zero Allocation On Sync Path

Key Differences
Task<T> ValueTask<T>
Type Class — heap Struct — stack
Allocation Always Only when truly async
Awaited twice ✅ Safe ❌ Never await twice
Complexity Simple Slightly more careful

When To Prefer ValueTask

Use ValueTask when the method frequently returns synchronously — cached data, computed values,
hot paths called thousands of times per second.

When To Stick With Task

Use Task everywhere else. ValueTask has restrictions — never await it twice, never store it, never use
.Result on it. Wrong usage causes subtle bugs.

How do you run multiple async operations in parallel? Compare

Q31 Medium
[Link] vs [Link].
Ans Async Operations In Parallel
Instead of awaiting each task one by one sequentially, you start all tasks first then await them
together. This runs them in parallel.

Confidential — For Interview Preparation Only 39 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

[Link] — Wait For Everything

Completes when all tasks finish. Use when you need every result before moving forward.

[Link] — First One Wins

Completes when any one task finishes. Perfect for timeouts or racing multiple sources.

Key Difference
WhenAll WhenAny
Completes when All tasks done First task done
Use case Need all results Timeout, fastest wins

What is CancellationToken? How do you propagate cancellation through an

Q32 Medium
async call chain?
Ans CancellationToken
CancellationToken is a signal that travels through your async call chain saying — the caller no longer
needs this result, stop working. Common scenarios are user cancels a request, request timeout, or
application shutting down.

Propagating Through Call Chain

Confidential — For Interview Preparation Only 40 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

In [Link] Core — Free Cancellation Token

[Link] Core automatically provides a token via HttpContext. When user disconnects or request
times out — token is cancelled automatically.

What happens when you await a Task that has already completed? Does
Q33 Hard
context switch happen?
Ans If you await a Task that is already completed, the await does NOT suspend the method. It continues
synchronously inline — no thread switch, no context capture, no state machine pause.

How Awaiter Checks This

Where This Matters — Cache Example

Confidential — For Interview Preparation Only 41 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Hot path hits cache — await completes inline, no overhead. Cold path hits database — real suspension
happens.

Does Context Switch Happen?

No. The runtime calls [Link] first. If true — it calls GetResult() immediately and moves
on. The synchronization context is never captured, never restored. No overhead at all.

▶ 2.2 Concurrency & Threading

# Question Level
What is the difference between lock(), Monitor, Mutex, and SemaphoreSlim?
Q34 Hard
When to use each?
Ans When multiple threads access shared data simultaneously, you get race conditions — unpredictable,
corrupted results. Synchronization primitives ensure only the right number of threads access shared
resources at a time.

lock() — Simplest, Same Process Only

lock is syntactic sugar over Monitor. Allows only one thread at a time into a block. Fast, simple, but
blocks the thread.

Never lock on this or a public object — anyone can lock on it causing deadlocks.

Monitor — lock() With More Control

Monitor is what lock compiles to — but gives you timeout and TryEnter which lock does not.

Confidential — For Interview Preparation Only 42 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Mutex — Across Processes

Mutex works like lock but across multiple processes. Heavier and slower — use only when you need
cross process synchronization.

SemaphoreSlim — Allow N Threads, Async Friendly

SemaphoreSlim allows multiple threads up to a limit. The only primitive that works with async/await
properly — others block the thread.

When To Use Each

lock Monitor Mutex SemaphoreSlim
Use when Simple sync Need timeout Cross process Async code

Explain race conditions with a code example. How would you fix it using
Q35 Hard
Interlocked vs lock?
Ans When two threads read and write shared data simultaneously, results become unpredictable — each
thread overwrites the other's work.

Confidential — For Interview Preparation Only 43 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Why? _counter++ is actually three steps — read value, add 1, write back. Two threads can read the
same value simultaneously and both write back — one increment is lost.

Fix 1 — Interlocked — Atomic Operations

Interlocked performs the entire read-modify-write as a single atomic CPU operation. Fastest fix for
simple counters.

Fix 2 — lock — Broader Protection

Use lock when protecting more complex operations — multiple lines that must run together atomically.

When To Use Which

Interlocked lock
Use when Single variable operations Multiple operations together
Performance Faster Slightly slower
What is the ThreadPool? How does [Link] Core use it for HTTP
Q36 Medium
requests?
Ans ThreadPool
ThreadPool is a managed pool of pre-created threads maintained by the .NET runtime. Instead of
creating and destroying a thread for every task — which is expensive — the runtime reuses threads
from the pool.

How It Works

Confidential — For Interview Preparation Only 44 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

How [Link] Core Uses It

Every incoming HTTP request is handled by a thread borrowed from the ThreadPool. When request
finishes, thread returns to pool — ready for next request.

Request 1 arrives → Thread 1 borrowed from pool

Request 2 arrives → Thread 2 borrowed from pool
Request 3 arrives → Thread 3 borrowed from pool

Request 1 completes → Thread 1 returned to pool ✅

Request 4 arrives → Thread 1 reused ✅

Q37 What is the difference between [Link]() and [Link]() inside a loop? Medium

Ans [Link]() — CPU Bound, Blocks Until Done

[Link] splits iterations across multiple threads simultaneously and blocks the calling thread until
all iterations complete. Designed for CPU heavy work.

[Link]() Inside Loop — Creates Individual Tasks

[Link] inside a loop creates a separate task for each iteration and returns immediately. You manage
completion yourself.

Key Difference
[Link] [Link] in loop
Blocks caller ✅ Yes ❌ No — use WhenAll
Async friendly ❌ No ✅ Yes

Confidential — For Interview Preparation Only 45 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Best for CPU bound work Mixed/async work

Thread control Automatic Manual

What is the volatile keyword in C#? When does it matter and what does it
Q38 Hard
NOT protect against?
Ans Volatile
Volatile tells the compiler and CPU — always read this variable from main memory, never cache it in
a CPU register or reorder it. It guarantees visibility across threads.

What volatile Does NOT Protect Against

volatile only guarantees visibility — not atomicity. It does NOT protect compound operations like
increment.

What It Protects vs What It Does Not

volatile
Fresh reads across threads ✅ Yes
Instruction reordering ✅ Prevents
Atomic compound operations ❌ No
Race conditions ❌ No
Replaces lock ❌ No

SECTION 3 — .NET CORE & [Link] CORE

▶ 3.1 Middleware & Request Pipeline

# Question Level
Explain the [Link] Core middleware pipeline. What is the difference
Q39 Medium
between Use(), Run(), and Map()?
Ans Middleware Pipeline

Confidential — For Interview Preparation Only 46 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Middleware is a chain of components that process every HTTP request and response. Each component
can run logic before and after passing the request to the next component.

Request → Logging → Auth → Routing → Controller

Response ← Logging ← Auth ← Routing ← Controller

Use() — Runs And Passes To Next

Use adds middleware that runs logic and calls next to pass request down the pipeline.

Run() — Terminal, Never Passes Forward

Run adds a terminal middleware — it ends the pipeline. next is never called.

Map() — Branches Pipeline By Path

Map creates a separate pipeline branch for a specific URL path.

Key Difference
Use() Run() Map()
Passes to next ✅ Yes ❌ No Branches
Terminal ❌ No ✅ Yes Per branch
Use when General middleware End pipeline Path branching

Confidential — For Interview Preparation Only 47 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

What is the order of middleware execution and why does order matter? Give
Q40 Hard
an example of a bug caused by wrong order.
Ans Middleware Executes In Registration Order
Request flows top to bottom through middleware. Response flows bottom to top. The order you
register middleware in [Link] is the exact order it executes.

Request
↓
1. Logging
↓
2. Authentication
↓
3. Authorization
↓
4. Controller
↓
3. Authorization
↑
2. Authentication
↑
1. Logging
↑
Response

Why Order Matters

Each middleware assumes the previous one has already done its job. Authentication must run before
Authorization — you cannot check permissions before knowing who the user is.

Real Bug — Wrong Order

Another Real Bug — CORS Wrong Order

Confidential — For Interview Preparation Only 48 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Correct [Link] Core Order

How would you write a custom middleware to log request/response time and
Q41 Medium
add a correlation ID header?
Ans The Custom Middleware

Confidential — For Interview Preparation Only 49 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Extension Method — Clean Registration

Register In [Link]

Confidential — For Interview Preparation Only 50 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

What is the difference between filters (ActionFilter, ExceptionFilter) and

Q42 Hard
middleware? Which runs first?
Ans Difference
Middleware runs on every HTTP request — even static files, unknown routes, health checks. Filters
run only inside the MVC pipeline — only on controller actions. Middleware runs first.

Execution Order

Request
↓
Middleware 1 (Logging)
↓
Middleware 2 (Auth)
↓
MVC Pipeline starts
↓
Authorization Filter
↓
Action Filter — OnActionExecuting
↓
Controller Action executes
↓
Action Filter — OnActionExecuted
↓
Exception Filter (if error)
↓
Result Filter
↓
Response travels back up

ActionFilter — Runs Around Controller Action

Confidential — For Interview Preparation Only 51 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
ExceptionFilter — Catches Action Exceptions Only

Register Filters

When To Use Which

Middleware Filter
Scope Entire pipeline MVC actions only
Use for Logging, CORS, Auth, Correlation Validation, action logging, model
ID errors
Exception All exceptions Action exceptions only
handling
Runs first ✅ Yes ❌ After middleware

Explain endpoint routing in .NET 6+. How is it different from conventional

Q43 Medium
routing?
Ans Endpoint Routing
Endpoint routing separates route matching from endpoint execution. Routes are resolved early in the
pipeline — before authorization and middleware run — so middleware can inspect the matched
endpoint and make decisions.

Confidential — For Interview Preparation Only 52 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Conventional Routing
Conventional routing uses a URL pattern template defined globally. Every request is matched against
that pattern — controller and action names determine the route.

Key Differences
Conventional Endpoint Routing
Route resolved Late — after middleware Early — before middleware
Middleware sees route ❌ No ✅ Yes
Minimal APIs ❌ No ✅ Yes
Flexibility Limited High

▶ 3.2 Dependency Injection

# Question Level
Explain the three DI lifetimes: Transient, Scoped, Singleton. Give a real bug
Q44 Hard
caused by using Singleton for a DbContext.
Ans Transient — New Instance Every Time
A new instance is created every time it is requested from the container. Use for lightweight stateless
services.

Confidential — For Interview Preparation Only 53 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Scoped — One Instance Per Request

One instance created per HTTP request. Same instance shared within that request — new instance for
next request.

Singleton — One Instance Forever

One instance created for entire application lifetime. Shared across all requests and all threads.

Real Bug — Singleton DbContext

Confidential — For Interview Preparation Only 54 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Q45 What is a Captive Dependency? How do you detect and prevent it? Hard

Ans Captive Dependency

A captive dependency happens when a longer-lived service captures a shorter-lived service. The
shorter-lived service gets trapped — living longer than it should, causing stale data, threading issues, or
memory leaks.

Classic Example — Singleton Capturing Scoped

OrderRepository is scoped — it should die after each request. But Singleton OrderService holds it alive
forever. Every request uses the same stale repository — same DbContext, same tracked entities,
memory grows forever.

How To Detect It
[Link] Core Warns You

How To Fix It
Match Lifetimes

Captive Dependency Risk Table

Singleton consumes Risk
Singleton ✅ Safe
Scoped 🔴 Captive dependency
Transient ⚠️ Transient becomes singleton

Always enable ValidateOnBuild in all environments — not just development. It catches captive
dependencies the moment the app starts, before any request is served. Catching it at startup costs
nothing — catching it in production costs everything.

Confidential — For Interview Preparation Only 55 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
How do you inject multiple implementations of the same interface and
Q46 Medium
resolve them selectively?
Ans The Scenario
Sometimes you have multiple implementations of the same interface — multiple payment gateways,
multiple notification channels — and you need to pick the right one at runtime.

Register Multiple Implementations

Resolve All — IEnumerable<T>

Cleaner Approach — Factory Pattern

Confidential — For Interview Preparation Only 56 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Each Gateway Has A Name

Confidential — For Interview Preparation Only 57 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What is IServiceScopeFactory and when would you use it inside a Singleton
Q47 Hard
or background service?
Ans IServiceScopeFactory
IServiceScopeFactory creates a manual DI scope — letting you safely resolve Scoped services like
DbContext from inside a Singleton or background service that lives longer than a single request.

The Problem Without It

The Fix — IServiceScopeFactory

Real World Use Cases

Confidential — For Interview Preparation Only 58 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
1. Background job processing queue
2. Scheduled tasks — cleanup, reports, emails
3. Event handlers in Singleton services
4. Hosted services processing messages from RabbitMQ or Azure Service Bus

Register Background Service

IServiceScopeFactory is the correct solution whenever a long lived service needs database access. In
my projects every BackgroundService follows this pattern — create scope, resolve what you need, do
the work, dispose scope. This guarantees DbContext is never shared across operations and
connections return to pool properly.

Can you inject services into a static class or a class not managed by the DI
Q48 Medium
container? How?
Ans No — static classes and unmanaged classes cannot use constructor injection because the DI container
never creates them. But there are workarounds.

Problem

Workaround 1 — Pass Service As Parameter

Workaround 2 — Service Locator Via IServiceProvider

Confidential — For Interview Preparation Only 59 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

This works but Service Locator is considered an anti-pattern — it hides dependencies and makes
testing harder.

Workaround 3 — Convert To Instance Class

When Each Applies

Approach Use When
Method parameter Occasional use, simple helper
Service Locator Legacy code, truly unavoidable
Convert to instance ✅ Always preferred

Confidential — For Interview Preparation Only 60 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

▶ 3.3 REST APIs & Web API Design

# Question Level
What is the difference between [FromBody], [FromQuery], [FromRoute], and
Q49 Easy
[FromForm]? Explain model binding.
Ans FromRoute — From URL Path

FromQuery — From URL Query String

FromBody — From Request JSON Body

FromForm — From Form Data / File Upload

Model Binding
Model binding is how [Link] Core automatically maps HTTP request data to action method
parameters — so you work with C# objects instead of raw HTTP strings.

Quick Reference
Attribute Reads From Use For
FromRoute URL path Resource ID
FromQuery Query string Filters, pagination
FromBody JSON body Create/update objects
FromForm Form data File uploads

How does model validation work in [Link] Core? How do you create a
Q50 Medium
custom ValidationAttribute?
Ans Model Validation Works
[Link] Core automatically validates incoming models using Data Annotations. When [ApiController]
is present, if validation fails it returns a 400 Bad Request automatically — no manual checking needed.

Built-in Validation Attributes

Confidential — For Interview Preparation Only 61 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

How It Flows
Request arrives with JSON body
↓
Model binding maps JSON → RegisterDto
↓
Validation attributes checked automatically
↓
[ApiController] — ModelState invalid?
↓
❌ Returns 400 Bad Request automatically
✅ Passes to action if valid

Without ApiController — Manual Check

Custom ValidationAttribute
When built-in attributes are not enough — create your own by extending ValidationAttribute.

Confidential — For Interview Preparation Only 62 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Use Custom Attributes

For complex cross-property validation — like confirming password matches password confirmation —
Use IValidatableObject on the DTO itself instead of a custom attribute. It gives access to the entire
object during validation, which a single property attribute cannot do.

Confidential — For Interview Preparation Only 63 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What is IActionResult vs ActionResult<T>? What are the advantages of using
Q51 Medium
ActionResult<T>?
Ans IActionResult — Flexible But Type Blind
IActionResult can return any HTTP response — Ok, NotFound, BadRequest. But the actual return
type is lost — Swagger cannot infer it, callers do not know what to expect.

ActionResult<T> — Flexible AND Type Safe

ActionResult<T> gives you both — flexibility to return HTTP responses AND strong typing for the
success response. Swagger automatically documents the return type.

Advantages Of ActionResult<T>
1 — Swagger Documents It Automatically

2 — Implicit Conversion — Cleaner Code

3 — Unit Testing Is Cleaner

Side By Side
IActionResult ActionResult<T>
Return type known ❌ No ✅ Yes
Swagger support ❌ Manual attributes ✅ Automatic
Implicit conversion ❌ No ✅ Yes

Confidential — For Interview Preparation Only 64 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Multiple responses ✅ Yes ✅ Yes

Unit test access ❌ Must cast ✅ Direct .Value

Explain repeat-safe behavior in REST APIs. Which HTTP verbs are repeat-
Q52 Hard
safe and why does it matter for reliability?
Ans Repeat-Safe Behavior
Repeat-safe means calling the same request multiple times produces the same result as calling it
once. The technical term is idempotency. It matters because networks fail — clients retry requests,
and retrying must not cause duplicate data or side effects.

Which HTTP Verbs Are Idempotent

GET /orders/1 → Always returns same order ✅ Idempotent
PUT /orders/1 → Always sets same state ✅ Idempotent
DELETE /orders/1 → Delete once or 10 times = gone ✅ Idempotent
PATCH /orders/1 → Depends on implementation ⚠️ Sometimes
POST /orders → Creates new order every call ❌ Not idempotent

Why POST Is Dangerous On Retry

Fix — Idempotency Key

PUT vs PATCH Idempotency

Confidential — For Interview Preparation Only 65 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Why It Matters In Production

Payment gateways, order systems, booking APIs — all face network failures. Without idempotency a
customer gets charged twice for one order. With idempotency retries are safe — the system recognizes
duplicate requests and ignores them.

How do you implement API versioning in [Link] Core? What are the
Q53 Medium
tradeoffs of URL vs header versioning?
Ans API Versioning
When you change an API that clients already depend on, you need versioning — so existing clients
keep working while new clients use the improved version.

Setup — Install Package

dotnet add package [Link]

Approach 1 — URL Versioning

GET /api/v1/orders → V1 controller

GET /api/v2/orders → V2 controller

Confidential — For Interview Preparation Only 66 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Approach 2 — Header Versioning

GET /api/orders
X-API-Version: 1.0 → V1 controller
X-API-Version: 2.0 → V2 controller

Approach 3 — Query String Versioning

GET /api/orders?api-version=1.0 → V1
GET /api/orders?api-version=2.0 → V2

Tradeoffs
URL Header Query String
Visibility ✅ Very clear ❌ Hidden ✅ Visible
Cacheable ✅ Easy ❌ Harder ✅ Yes
REST purist ❌ URL should not change ✅ Cleaner URLs ❌ Not clean
Browser friendly ✅ Yes ❌ No ✅ Yes
Most common ✅ Industry standard Internal APIs Simple APIs

Always use URL versioning for public APIs — it is explicit, cacheable, and works perfectly in browsers
and Swagger. Header versioning I use for internal microservice APIs where clean URLs matter more
than visibility. The most important thing is picking one strategy and applying it consistently.

How do you handle global exception handling in [Link] Core? Compare

Q54 Hard
ExceptionHandler middleware vs ProblemDetails.
Ans In [Link] Core, handle global exception handling using centralized middleware so that all unhandled
exceptions are managed from one place instead of writing try-catch blocks in every controller.
Usually, use:
 Exception Handling Middleware for catching exceptions globally
 ProblemDetails for returning standardized and clean API error responses

1. ExceptionHandler Middleware
[Link] Core provides built-in middleware:

or custom middleware:

Confidential — For Interview Preparation Only 67 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Why use it
 Centralized exception handling
 Cleaner controllers
 Better logging
 Prevents exposing sensitive error details

2. ProblemDetails
ProblemDetails is a standard error response format based on RFC 7807.
Example response:

In .NET:

Why use it
 Standardized API responses
 Easy for frontend/mobile apps to understand
 Better API consistency
 Professional REST API design

What is Minimal API in .NET 6+? When would you choose it over Controller-
Q55 Medium
based APIs?

Confidential — For Interview Preparation Only 68 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Ans Minimal API in .NET 6+
Minimal API is a lightweight way to build APIs in [Link] Core with less boilerplate code and minimal
setup.
Instead of creating:
 Controllers
 Separate action methods
 Large configuration files
everything can be written directly inside [Link].
Example:

Advantages of Minimal API

 Less code
 Faster development
 Better performance
 Easy to understand
 Good for lightweight services

When to Choose Minimal API

Minimal APIs are preferred when building:
 Small applications
 Microservices
 Simple CRUD APIs
 Lightweight backend services
 Fast prototypes
 Serverless APIs

When to Choose Controller-Based APIs

Controller-based APIs are better for:
 Large enterprise applications
 Complex business logic
 Role-based authorization
 Versioning
 Filters
 Better separation of concerns
 Maintainable long-term projects

How do you implement rate limiting in [Link] Core? Explain the built-in
Q56 Hard
rate limiting middleware in .NET 7+.
Ans Rate limiting in [Link] Core?
Rate limiting is used to control how many requests a client can send within a specific time.
It helps to:
 Prevent API abuse
 Avoid server overload
 Improve security

Confidential — For Interview Preparation Only 69 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

 Protect against brute-force attacks

In .NET 7+, [Link] Core provides built-in Rate Limiting Middleware.

Built-in Rate Limiting Middleware in .NET 7+

First, configure rate limiting in [Link].

Enable middleware:

Apply it to endpoint:

What This Does

Maximum 5 requests
Within 1 minute
After limit → 429 Too Many Requests

▶ 3.4 Authentication & Authorization

# Question Level
Explain JWT authentication in [Link] Core. What are the three parts of a
Q57 Medium
JWT and what does each contain?
Ans JWT Authentication in [Link] Core
JWT (JSON Web Token) authentication is a token-based authentication mechanism used to secure
APIs.
After successful login:
 Server generates a token
 Client stores the token
 Client sends the token in every request
 Server validates the token before allowing access
Usually sent in header:
Authorization: Bearer <token>

JWT Authentication Flow

User Login
↓

Confidential — For Interview Preparation Only 70 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Server validates credentials
↓
JWT Token generated
↓
Client stores token
↓
Client sends token with requests
↓
Server validates token
↓
Access granted

Three Parts of JWT

A JWT contains 3 parts separated by dots:
[Link]

1. Header
Contains:
 Token type
 Signing algorithm
Example:

2. Payload
Contains claims or user data.
Example:
 UserId
 Email
 Roles
 Expiration time

3. Signature
Used to verify that token is valid and not modified.
Generated using:
 Header
 Payload
 Secret key
This provides security and integrity.

What is the difference between Authentication and Authorization? Explain

Q58 Easy
claims-based identity.
Ans Difference Between Authentication and Authorization
Authentication Authorization

Confidential — For Interview Preparation Only 71 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Verifies who the user is Verifies what the user can access
Happens first Happens after authentication
Checks identity Checks permissions
Example: Login with username/password Example: Access Admin dashboard
Returns user identity Returns access rights

Simple Real-Time Example

Authentication → Who are you?
Authorization → What are you allowed to do?

Authentication in [Link] Core

Authentication methods:
 JWT
 Cookies
 OAuth
 Identity
Example:

Authorization in [Link] Core

Authorization checks roles, claims, or policies.
Example:

Role-based:

Claims-Based Identity?
Claims-based identity stores user information as claims.
A claim is a key-value pair containing information about the user.
Examples:
 Name
 Email
 Role
 Department
 UserId

Example of Claims

What is the difference between [Authorize] on a controller vs middleware-

Q59 Hard
level auth? What is the order of execution?
Ans Difference Between [Authorize] Attribute and Middleware-Level Authentication
[Authorize] Attribute Middleware-Level Authentication
Applied on controllers/actions Applied globally in request pipeline
Checks access permission Validates and sets user identity
Used for protecting specific endpoints Used for entire application authentication flow

Confidential — For Interview Preparation Only 72 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Works after authentication middleware Runs before controller execution

Middleware-Level Authentication
Authentication middleware validates the token or cookie and creates the user identity.
Example:

Responsibilities:
 Read JWT token/cookie
 Validate credentials
 Create [Link]
Without this middleware:
 [Authorize] will not work properly

[Authorize] Attribute
Used to protect controllers or actions.
Example:

Role-based:

Responsibilities:
 Check if user is authenticated
 Check roles/policies/claims
 Allow or deny access

Order of Execution
Correct middleware order is very important.

Execution Flow:

Request arrives
↓
Routing Middleware
↓
Authentication Middleware
↓
Authorization Middleware
↓
[Authorize] attribute checks permissions
↓
Controller Action executes

Confidential — For Interview Preparation Only 73 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
How do you implement refresh tokens securely? What are the security
Q60 Hard
considerations?
Ans Implement refresh tokens securely
Refresh tokens are used to generate new access tokens without asking the user to log in again.
Usually:
 Access token → short expiry (15–30 mins)
 Refresh token → long expiry (days/weeks)
When access token expires:
 Client sends refresh token
 Server validates it
 New access token is generated

Basic Flow
User Login
↓
Generate Access Token + Refresh Token
↓
Store Refresh Token securely
↓
Access Token expires
↓
Client sends Refresh Token
↓
Server validates it
↓
Generate new Access Token

Secure Refresh Token Implementation

1. Store Refresh Tokens in Database

Store:
 Token value
 Expiry date
 User ID
 Device/session info
Example:

2. Use Long Random Tokens

Refresh tokens should be:
 Cryptographically secure
 Random
 Hard to guess
Example:

3. Set Expiry Time

Confidential — For Interview Preparation Only 74 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Refresh tokens should expire.
Example:
 7 days
 30 days
Never create non-expiring tokens.

4. Revoke Old Tokens

When new refresh token is issued:
 Old refresh token should be invalidated
This is called refresh token rotation.

5. Store Tokens Securely

Best practice:
 Store in HttpOnly cookies
 Avoid localStorage for sensitive apps
HttpOnly cookies help prevent XSS attacks.

Security Considerations
Security Concern Solution
Token theft Use HTTPS
XSS attacks Use HttpOnly cookies
Replay attacks Refresh token rotation
Long-lived token misuse Short expiry
Database leaks Hash refresh tokens before storing
Unauthorized reuse Revoke tokens on logout

What is OAuth 2.0? Explain the Authorization Code flow with PKCE and
Q61 Hard
when to use it.
Ans OAuth 2.0?
OAuth 2.0 is an authorization framework that allows an application to access a user’s resources on
another service without sharing the user’s password.
Instead of credentials, it uses:
 Access tokens
 Refresh tokens (optional)
It is commonly used for:
 “Login with Google / Microsoft”
 API authorization between services

Core Idea
User → Grants permission → App gets token → Access API securely

Authorization Code Flow with PKCE

PKCE (Proof Key for Code Exchange) is an enhanced and more secure version of Authorization Code
Flow.
It is mainly used for public clients (no client secret).

Step-by-Step Flow
1. Client creates PKCE values
 Code Verifier (random string)
 Code Challenge (hashed version)

Confidential — For Interview Preparation Only 75 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

2. User is redirected to Authorization Server

User logs in and grants permission.

3. Authorization Code is returned

Server sends:
 Authorization code (temporary)

4. Client sends code + verifier

Client sends:
 Authorization code
 Code verifier
Server validates them.

5. Tokens are issued

Server returns:
 Access token
 Refresh token (optional)

When to Use Authorization Code Flow with PKCE

Best used for:

1. Mobile Applications
 Android / iOS apps

2. Single Page Applications (SPA)
 Angular / React apps

3. Public Clients
 No ability to securely store client secret

What is the difference between Role-based and Policy-based authorization

Q62 Medium
in [Link] Core?
Ans Difference between Role-based and Policy-based Authorization in [Link] Core
Both are used to control access to resources, but they differ in flexibility and design approach.

1. Role-based Authorization
Role-based authorization checks user roles only.

Example:

How it works:
 User is assigned roles like Admin, User, Manager
 Access is granted based on matching role

2. Policy-based Authorization
Policy-based authorization is more flexible and rule-driven.
Instead of checking only roles, it checks custom conditions (claims, logic, requirements).

Example:

Confidential — For Interview Preparation Only 76 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Step 1: Define policy

Step 2: Use policy

How it works:
 Policies can check:
o Claims
o Roles
o Custom logic
o Multiple conditions

SECTION 4 — ENTITY FRAMEWORK CORE

▶ 4.1 EF Core Internals & Configuration

# Question Level
What is the difference between Code First, Database First, and Model First
Q63 Easy
approaches in EF Core?
Ans Entity Framework provides different approaches to work with databases depending on how the project
starts: code, database, or model.

1. Code First Approach

In Code First, the database is created from C# classes (entities).
How it works:
 Write entity classes
 Define DbContext
 Run migrations
 Database is generated automatically
Example:

Advantages:

Confidential — For Interview Preparation Only 77 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

 Full control over code

 Easy versioning using migrations
 Best for new projects
 Works well with clean architecture
Disadvantages:
 Requires learning migrations
 Not suitable when DB already exists

2. Database First Approach

In Database First, the database already exists, and models are generated from it.
How it works:
 Create database first
 Use scaffolding command
 EF generates classes automatically
Example command:

Advantages:
 Best for legacy databases
 Quick setup for existing DB
 No need to design models manually
Disadvantages:
 Less control over code
 Regeneration needed if DB changes
 Not ideal for clean architecture

3. Model First Approach

In Model First, the model is designed visually first, and then database is generated.
How it works:
 Create ER diagram/model in designer
 EF generates database schema

Advantages:
 Visual design approach
 Easy for beginners in simple projects
Disadvantages:
 Limited in modern EF Core
 Rarely used today
 Not flexible for large systems

Explain the EF Core Change Tracker. What is the difference between

Q64 Medium
Added, Modified, Unchanged, and Deleted states?
Ans EF Core Change Tracker (What it is)
EF Core Change Tracker is a mechanism that keeps track of entity objects in memory and monitors
how they change after being loaded from the database.
When SaveChanges() is called, EF Core uses these tracked states to decide:
 What to insert
 What to update
 What to delete

Confidential — For Interview Preparation Only 78 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

 What to ignore

1. Added
Entity is new and will be inserted into the database.
Example:

Behavior:
 INSERT query will be generated
 Primary key usually generated by DB

2. Modified
Entity already exists and has been changed.
Example:

Behavior:
 UPDATE query will be generated
 Only changed fields are updated

3. Unchanged
Entity is tracked but no changes detected.
Example:

Behavior:
 No SQL operation executed
 EF ignores it during SaveChanges

4. Deleted
Entity is marked for removal from database.
Example:

Behavior:
 DELETE query will be generated
 Removed after SaveChanges()

What is AsNoTracking()? When should you always use it and why can it
Q65 Medium
cause bugs if misused?
Ans AsNoTracking() in EF Core?
AsNoTracking() tells EF Core not to track the entity in the Change Tracker.
Normally, EF Core tracks every entity so it can detect changes and update the database later. With
AsNoTracking(), EF only reads data without tracking it.

Example:

Confidential — For Interview Preparation Only 79 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

When should AsNoTracking() be used?

1. Read-only operations (best use case)
Example:
 Listing products
 Reports
 Dashboards

2. High-performance queries
 Large datasets
 APIs returning data only
 Search results

3. CQRS pattern (Query side)

 Read side should not track entities

How it can cause bugs if misused

1. Updates will NOT work

Example:

Why?
EF Core is not tracking the entity, so it doesn’t detect changes.

2. Navigation properties issues

 Related entities may not be loaded or tracked properly
 Lazy loading may not work as expected

3. Confusion in business logic

 Developer thinks entity is tracked
 But EF ignores all modifications

What is the N+1 query problem? Give a concrete example and show how to
Q66 Hard
fix it with Include() or explicit loading.
Ans N+1 Query Problem
The N+1 problem happens when an application executes:
 1 query to get main data
 N additional queries to get related data

So total queries = 1 + N, which causes performance issues.

It usually occurs when lazy loading or improper querying is used.

Confidential — For Interview Preparation Only 80 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Concrete Example (Problem Scenario)

Assume two tables:
 Orders
 OrderItems
Each order has multiple items.

Bad Example (N+1 Problem)

What is happening here?

If 100 orders exist:

 1 + 100 queries = 101 queries ❌

Problem Impact
 Slow performance
 High database load
 Increased latency
 Not scalable for large data

Solution 1: Using Include() (Eager Loading)

Best and most common fix.

What happens now?

1 query → Orders + OrderItems (JOIN)
✔ Only 1 query executed
✔ Much faster performance

Solution 2: Explicit Loading

Used when related data is loaded manually but efficiently.

Confidential — For Interview Preparation Only 81 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Behavior:
 Still multiple queries
 But controlled and intentional
 Better than naive per-query access

Solution Comparison
Approach Queries Performance Use Case
Bad (loop query) 1 + N Poor ❌ Avoid
Include() 1 Best Most common
Explicit Loading Controlled N Medium Advanced scenarios

How does EF Core handle concurrency? Explain optimistic concurrency

Q67 Hard
with [ConcurrencyCheck] and RowVersion.
Ans What Is Concurrency Problem
When two users load the same record and both try to save changes — the second save silently
overwrites the first. Last write wins — data is lost with no warning.

User A loads Order #1 — Amount: 500

User B loads Order #1 — Amount: 500

User A changes Amount to 600 — saves ✅

User B changes Amount to 700 — saves ✅ overwrites A silently

Optimistic Concurrency — Detect, Don't Lock

Instead of locking the record, optimistic concurrency detects the conflict at save time and throws an
exception — letting you handle it gracefully.

Approach 1 — ConcurrencyCheck Attribute

EF generates this UPDATE

UPDATE Orders
SET Amount = 700, LastModified = '2024-01-02'
WHERE Id = 1
AND LastModified = '2024-01-01' -- ✅ Checks original value
If row changed since load — 0 rows affected — conflict detected!

Approach 2 — RowVersion (Recommended)

Confidential — For Interview Preparation Only 82 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Handling The Conflict

ConcurrencyCheck vs RowVersion
ConcurrencyCheck RowVersion
How it works Checks specific column value Auto-incremented byte stamp
Manual update needed ✅ Yes ❌ SQL handles it
Reliability Moderate ✅ Higher
Best for Specific fields Entire row protection

Confidential — For Interview Preparation Only 83 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What is a database migration? What happens if you delete a migration that
Q68 Hard
has already been applied to production?
Ans Database Migration
A migration is a versioned C# file that describes how the database schema should change — create
table, add column, drop index. EF Core tracks which migrations have been applied so schema stays in
sync with your models.

Basic Migration Commands

What Happens If You Delete An Applied Migration

EF Core tracks applied migrations in a __EFMigrationsHistory table in your database. If you delete the
migration file but the database still has its record — EF Core loses track completely.

__EFMigrationsHistory table in Production DB:

┌───────────────────────────────┐
│ 20240101_InitialCreate │
│ 20240115_AddDeliveryDate ← exists in DB, file deleted! 💥
│ 20240120_AddPaymentStatus │
└──────────────────────────────────┘
EF Core now thinks schema is out of sync
Next migration add → conflicts with existing columns
dotnet ef database update → 💥 errors or data loss

Treat migrations exactly like Git commits — you never rewrite history that others have already pulled. If
made a mistake in an applied migration, create a new corrective migration instead of touching the old
one. This keeps every environment — local, staging, production — perfectly in sync.

Explain the difference between Eager Loading, Lazy Loading, and Explicit
Q69 Hard
Loading in EF Core. What are the risks of Lazy Loading?
Ans Eager Loading — Load Everything Upfront
Related data is loaded immediately with the main query using Include. One SQL query with JOIN —
everything arrives together.

-- Generated SQL — single JOIN query

Confidential — For Interview Preparation Only 84 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
SELECT * FROM Orders
JOIN OrderItems ON [Link] = [Link]
JOIN Customers ON [Link] = [Link]

Lazy Loading — Load On Access

Related data loads automatically when you access the navigation property — EF Core makes a
separate database call at that moment.

Explicit Loading — Load On Demand Manually

You decide exactly when and what to load by calling Load() explicitly.

Risks Of Lazy Loading — N+1 Problem

The biggest risk is the N+1 problem — one query to load N records, then N separate queries for each
related record. Silently destroys performance.

Confidential — For Interview Preparation Only 85 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

What is DbContext pooling (AddDbContextPool)? When does it help and

Q70 Hard
what restrictions does it impose?
Ans DbContext Pooling
Instead of creating and destroying a DbContext on every request, AddDbContextPool reuses
DbContext instances from a pool — similar to how connection pooling works. Reduces allocation
overhead on high traffic APIs.

Normal vs Pooled Registration

How It Works Internally

When It Helps
High traffic APIs with many short lived requests benefit most — reduces GC pressure from constant
DbContext allocation and destruction.

Restrictions It Imposes

Restriction 1 — No Constructor Injection Of Scoped Services

Confidential — For Interview Preparation Only 86 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Restriction 2 — No Custom State In DbContext

When To Use What

AddDbContext AddDbContextPool
Performance Standard ✅ Higher
Custom state ✅ Safe ❌ Restricted
Scoped injection ✅ Safe ❌ Restricted
Best for Most apps High traffic APIs

How do you execute raw SQL in EF Core? What is the difference between
Q71 Medium
FromSqlRaw and ExecuteSqlRaw?

Confidential — For Interview Preparation Only 87 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Ans Two Types Of Raw SQL In EF Core
FromSqlRaw is for SELECT queries — returns entities tracked by EF Core.
ExecuteSqlRaw is for INSERT, UPDATE, DELETE — returns rows affected count, no entities returned.

FromSqlRaw — SELECT Queries

ExecuteSqlRaw — INSERT, UPDATE, DELETE

Key Difference
FromSqlRaw ExecuteSqlRaw
Use for SELECT INSERT, UPDATE, DELETE
Returns Entities Rows affected count
EF Tracking ✅ Yes ❌ No
LINQ chainable ✅ Yes ❌ No

How do you configure a many-to-many relationship in EF Core 5+ vs older

Q72 Medium
versions?
Ans EF Core 5+ — No Join Entity Needed
EF Core 5 introduced direct many-to-many — you simply define navigation collections on both sides
and EF Core automatically creates the join table behind the scenes. No extra class needed.

Confidential — For Interview Preparation Only 88 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Older Versions — Explicit Join Entity Required

In older EF Core, many-to-many relationship required creating a join entity class manually to
represent the middle table. EF Core could not figure it out on its own.

If the join table needs extra columns like enrollment date, you still define an explicit join entity
even in EF Core 5+ — but for pure relationships the automatic approach keeps the code clean
and simple.

SECTION 5 — SQL SERVER DATABASE

Confidential — For Interview Preparation Only 89 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

▶ 5.1 Query Writing & Optimization

# Question Level
What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and
Q73 Easy
FULL OUTER JOIN? When does LEFT JOIN return NULLs unexpectedly?
Ans INNER JOIN — Only Matching Rows
Returns rows where there is a match in both tables. If no match exists on either side — row is
excluded completely.

SELECT [Link], [Link]

FROM Orders o
INNER JOIN Customers c ON [Link] = [Link]
-- Only orders that HAVE a customer returned
-- Orders with no customer — excluded

LEFT JOIN — All Left, Matching Right

Returns all rows from left table and matching rows from right. If no match on right side — NULLs fill
the right columns.

SELECT [Link], [Link]

FROM Orders o
LEFT JOIN Customers c ON [Link] = [Link]
-- All orders returned
-- Orders with no customer — Customer columns = NULL

RIGHT JOIN — All Right, Matching Left

Opposite of LEFT JOIN — returns all rows from right table. Rarely used — most developers rewrite it
as LEFT JOIN by swapping table order.

SELECT [Link], [Link]

FROM Orders o
RIGHT JOIN Customers c ON [Link] = [Link]
-- All customers returned
-- Customers with no orders — Order columns = NULL

FULL OUTER JOIN — Everything From Both

Returns all rows from both tables. NULLs fill missing sides on both left and right.

SELECT [Link], [Link]

FROM Orders o
FULL OUTER JOIN Customers c ON [Link] = [Link]
-- All orders AND all customers returned
-- Missing matches on either side = NULL

When LEFT JOIN Returns Unexpected NULLs

When you filter on a RIGHT side column in WHERE clause — it silently converts LEFT JOIN to INNER
JOIN behavior, excluding the NULL rows you expected to see.

-- 🔴 Unexpected — WHERE filters out NULL rows

SELECT [Link], [Link]
FROM Orders o

Confidential — For Interview Preparation Only 90 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
LEFT JOIN Customers c ON [Link] = [Link]
WHERE [Link] = 'Ahmed' -- Orders with no customer excluded! NULLs gone 💥

-- ✅ Fix — move condition to ON clause

SELECT [Link], [Link]
FROM Orders o
LEFT JOIN Customers c
ON [Link] = [Link] AND [Link] = 'Ahmed' -- NULLs preserved ✅

What is a CTE (Common Table Expression)? How does it differ from a

Q74 Medium
subquery in terms of readability and performance?
Ans CTE
A CTE is a temporary named result set defined at the top of a query using the WITH keyword. It
makes complex queries more readable by breaking them into named logical steps.

Same Query With Subquery

A subquery embeds the logic inline inside the main query — harder to read, especially when nested
multiple levels deep.

Readability — CTE Wins

CTE reads like a story — top to bottom, named logical steps. Subqueries read inside out — harder to
follow as complexity grows.

Confidential — For Interview Preparation Only 91 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Performance — Usually The Same

In most databases CTEs and subqueries produce identical execution plans — the query optimizer
treats them the same way. CTE is not faster by default.

Explain window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), and

Q75 Hard
NTILE(). Give a practical use case for each.
Ans ROW_NUMBER() — Unique Sequential Number
Assigns a unique sequential number to each row within a partition. No ties — every row gets a
different number.

RANK() — Same Rank For Ties, Gaps After

Assigns same rank to tied rows but skips numbers after ties. Two rows ranked 1 — next rank is 3, not
2.

DENSE_RANK() — Same Rank For Ties, No Gaps

Confidential — For Interview Preparation Only 92 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Same as RANK but no gaps after ties. Two rows ranked 1 — next rank is 2.

NTILE(N) — Divide Rows Into N Buckets

Divides rows into N equal groups and assigns a bucket number. Perfect for percentiles and
performance tiers.

What is the difference between WHERE and HAVING? Can you use a
Q76 Medium
window function in a WHERE clause?
Ans WHERE — Filters Rows Before Grouping
WHERE filters individual rows before any grouping or aggregation happens. It cannot reference
aggregate functions.

HAVING — Filters Groups After Grouping

HAVING filters groups after aggregation. It can reference aggregate functions — WHERE cannot.

Confidential — For Interview Preparation Only 93 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Using Both Together

Can You Use Window Function In WHERE Clause?

No — window functions cannot be used directly in WHERE clause. WHERE is evaluated before
window functions are calculated. You must wrap it in a CTE or subquery first.

Explain CROSS APPLY vs OUTER APPLY. When would you use them over
Q77 Hard
a JOIN?
Ans What Is APPLY
APPLY lets you call a table-valued function or subquery for each row of the outer table —
something a regular JOIN cannot do. It is like a correlated loop between two result sets.

CROSS APPLY — Only Matching Rows

Returns rows from the left table only when the right side produces results. Behaves like INNER
JOIN but right side can reference left side columns.

Confidential — For Interview Preparation Only 94 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

OUTER APPLY — All Left Rows

Returns all rows from left table even when right side produces no results — NULLs fill the right side.
Behaves like LEFT JOIN.

When To Use APPLY Over JOIN

Use APPLY when the right side depends on each left row individually — something a regular JOIN
cannot express.

What is a recursive CTE? Write one to traverse a hierarchical employee

Q78 Hard
table.

Confidential — For Interview Preparation Only 95 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Ans Recursive CTE
A recursive CTE is a CTE that references itself — it keeps repeating until no more rows are returned.
Perfect for hierarchical data like org charts, category trees, or folder structures where depth is unknown.

The Employee Table

Recursive CTE — Traverse Full Hierarchy

Confidential — For Interview Preparation Only 96 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Result

How It Works — Two Parts

What is the difference between UNION and UNION ALL? When does UNION
Q79 Easy
cause performance problems?

Confidential — For Interview Preparation Only 97 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Ans UNION — Combines And Removes Duplicates
UNION combines results of two queries and removes duplicate rows. To remove duplicates it
performs a sort or hash operation internally — which costs extra CPU and memory.

UNION ALL — Combines Without Removing Duplicates

UNION ALL combines results and keeps all rows including duplicates. No deduplication work —
faster and cheaper than UNION.

When UNION Causes Performance Problems

UNION performs a distinct sort on the entire result set — when result set is large this becomes
expensive. On millions of rows the sort operation consumes significant CPU, memory, and time.

Key Difference
UNION UNION ALL
Duplicates Removed Kept
Performance Slower ✅ Faster
Use when Duplicates must go Duplicates acceptable

Explain the MERGE statement. What are its common pitfalls and race
Q80 Hard
condition risks?
Ans MERGE

Confidential — For Interview Preparation Only 98 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
MERGE combines INSERT, UPDATE, and DELETE into a single statement. It compares a source
against a target and performs different actions based on whether rows match or not — also called
upsert.

Common Pitfall 1 — Duplicate Source Rows

If source has duplicate matching rows, MERGE throws an error. Source must have unique rows for
the join condition.

Common Pitfall 2 — Race Condition Risk

MERGE is NOT atomic by default. Between the MATCH check and the actual INSERT/UPDATE,
another transaction can modify the same row — causing duplicate key errors or lost updates.

Confidential — For Interview Preparation Only 99 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Common Pitfall 3 — DELETE Removes Too Much

WHEN NOT MATCHED BY SOURCE with DELETE removes all target rows not in source — easy to
accidentally delete unintended data.

What is the difference between EXISTS and IN? When does IN fail with
Q81 Hard
NULLs?
Ans EXISTS — Checks If Rows Exist
EXISTS checks whether a subquery returns any rows at all — it stops as soon as it finds the first
match. It never compares actual values, just presence.

IN — Checks Value Against A List

IN checks whether a value matches any value in a list or subquery result. It evaluates the entire list
before comparing.

Confidential — For Interview Preparation Only 100 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
When IN Fails With NULLs — The Hidden Trap
This is the most dangerous behavior. If the subquery returns even one NULL value, IN returns
UNKNOWN for non-matching rows — silently returning zero results.

What are PIVOT and UNPIVOT? Write a query to pivot monthly sales data
Q82 Medium
into columns.
Ans What Is PIVOT
PIVOT rotates rows into columns — transforms unique values from one column into multiple column
headers. Useful for reporting and summary views."

What Is UNPIVOT
UNPIVOT is the opposite — rotates columns back into rows. Normalizes wide tables back into a long
format.

The Source Data

Confidential — For Interview Preparation Only 101 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

PIVOT Query — Rows To Columns

Result After PIVOT

UNPIVOT — Columns Back To Rows

Confidential — For Interview Preparation Only 102 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

▶ 5.2 Indexing & Execution Plans

# Question Level
What is the difference between a Clustered Index and a Non-Clustered
Q83 Medium
Index? Can a table have both?
Ans Clustered Index — The Table IS The Index
A clustered index physically sorts and stores the actual table data in index order. The leaf nodes of
the index contain the actual data rows — not pointers. A table can have only one clustered index
because data can only be physically sorted one way.

Non-Clustered Index — Separate Structure With Pointers

A non-clustered index is a separate structure from the table. Leaf nodes contain the indexed column
values plus a pointer back to the actual row. A table can have multiple non-clustered indexes.

Can A Table Have Both?

Yes — and most production tables do. One clustered index on primary key, multiple non-clustered
indexes on frequently searched columns.

Confidential — For Interview Preparation Only 103 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What is index selectivity? Why is an index on a boolean column almost
Q84 Hard
useless?
Ans Index Selectivity
Selectivity measures how unique the values in a column are. High selectivity means most values are
different — index is very useful. Low selectivity means values repeat constantly — index gives little
benefit.

Why Boolean Index Is Almost Useless

A boolean column has only two possible values — true or false. If you query WHERE IsActive = 1 and
80% of rows are active, SQL Server decides it is cheaper to scan the whole table than use the index.
The index eliminates almost nothing.

What is a Covering Index (Include columns)? How does it eliminate Key

Q85 Hard
Lookups?
Ans What Is A Key Lookup
When SQL Server finds rows using a non-clustered index, it then makes a second trip back to the
main table to fetch columns not in the index. This second trip is called a Key Lookup — expensive on
large results.

Confidential — For Interview Preparation Only 104 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What Is A Covering Index
A covering index includes all columns the query needs directly inside the index — so SQL Server never
needs to go back to the main table.

Why INCLUDE Instead Of Adding To Key

INCLUDE columns are stored only at the leaf level — keeps the index tree lean and fast while still
covering the query.

What is index fragmentation? What is the difference between REORGANIZE

Q86 Medium
and REBUILD?
Ans What Is Index Fragmentation
As rows are inserted, updated, and deleted, index pages become out of order and partially empty.
This forces SQL Server to do extra reads to fetch data — slowing down queries over time.

REORGANIZE — Light Online Defrag

REORGANIZE physically reorders index leaf pages online — table stays fully accessible during the
operation. Best for low to moderate fragmentation.

REBUILD — Full Reconstruction

REBUILD drops and recreates the index from scratch — completely eliminates fragmentation. More
expensive but thorough. In older SQL Server versions it locks the table

Confidential — For Interview Preparation Only 105 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

What causes a Table Scan vs Index Seek? Name five query patterns that
Q87 Hard
prevent index use.
Ans Table Scan vs Index Seek
An Index Seek jumps directly to matching rows using the index tree — fast and precise. A Table Scan
reads every single row in the table — slow on large data. SQL Server chooses based on whether it can
use an index efficiently.

5 Query Patterns That Prevent Index Use

1 — Function On Indexed Column

2 — Leading Wildcard In LIKE

3 — Implicit Type Conversion

4 — OR Across Different Columns

Confidential — For Interview Preparation Only 106 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

5 — NOT Equal Operators

What is a Composite Index? Explain the leading column rule and how
Q88 Hard
column order matters.
Ans Composite Index
A composite index is an index built on two or more columns together. SQL Server builds the index
tree using columns in the exact order you define them — order matters critically.

The Leading Column Rule

SQL Server can only use a composite index if the query filters on the leftmost column first. Skipping
the leading column makes the index unusable.

Confidential — For Interview Preparation Only 107 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Why Column Order Matters

Put the most selective and most frequently queried column first. High selectivity first narrows
results fastest — making every subsequent column filter cheaper.

How do you read an Execution Plan? What do the icons Table Scan, Index
Q89 Hard
Seek, Hash Join, Nested Loop, and Sort mean?
Ans What Is An Execution Plan
An execution plan shows exactly how SQL Server executes your query — which indexes it uses,
how it joins tables, where the cost is. It is the most powerful tool for diagnosing slow queries.

Table Scan
Reads every single row in the table — no index used. Always a red flag on large tables.

Index Seek

Confidential — For Interview Preparation Only 108 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Jumps directly to matching rows using index tree — fast and precise. What you always want to see.

Nested Loop Join

For each row in outer table, looks up matching rows in inner table. Best for small result sets —
becomes expensive when outer table is large.

Hash Join
Builds a hash table from smaller input, then probes it with larger input. Used when joining large
tables with no useful index. Memory intensive.

Sort
Physically sorts result set — expensive on large data. Appears when ORDER BY, DISTINCT,
GROUP BY cannot use an existing index.

What are statistics in SQL Server? How do stale statistics cause bad query
Q90 Hard
plans?
Ans Statistics
Statistics are metadata objects that tell SQL Server how data is distributed in a column — how many
rows exist, how unique values are spread, and what the data range looks like. SQL Server uses this
information to estimate row counts and choose the best execution plan.

Confidential — For Interview Preparation Only 109 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

How SQL Server Uses Statistics

How Stale Statistics Cause Bad Plans

When data changes significantly but statistics are not updated, SQL Server makes wrong row
estimates — choosing completely wrong execution plans.

How To Fix Stale Statistics

Confidential — For Interview Preparation Only 110 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Auto Update Statistics

SQL Server auto updates statistics — but only after 20% of rows change on large tables. On a 100
million row table that means 20 million changes before auto update triggers — a long time with bad
plans.

What is the difference between a filtered index and a partial index? When
Q91 Medium
would you use one?
Ans What Is A Filtered Index
A filtered index is a non-clustered index with a WHERE clause — it only indexes a subset of rows that
match the filter condition. Smaller, faster, and cheaper to maintain than a full index.

How It Helps Performance

Confidential — For Interview Preparation Only 111 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

When To Use A Filtered Index

Partial Index
Partial index is simply another name for filtered index — same concept, different terminology.
PostgreSQL calls it partial index, SQL Server calls it filtered index.

▶ 5.3 Transactions, Locking & Concurrency

# Question Level
Explain the ACID properties. Give a real scenario where violating each
Q92 Medium
would cause problems.
Ans What Are ACID Properties
ACID is a set of four properties that guarantee database transactions are processed reliably and
correctly even in failures or concurrent access.

A — Atomicity
All operations in a transaction succeed together or fail together — never partially applied.

Confidential — For Interview Preparation Only 112 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

❌ Violation scenario:
-- Debit succeeds, server crashes before Credit
-- Account 1 lost 500 — Account 2 never received it 💥
-- Money disappeared from the system

C — Consistency
Transaction must bring database from one valid state to another — all rules, constraints, and
cascades always satisfied.

❌ Violation scenario:
-- Order created with CustomerId = 9999
-- Customer 9999 does not exist — foreign key violated
-- Database now has orphaned order — referential integrity broken 💥

I — Isolation
Concurrent transactions must not interfere with each other — each transaction sees a consistent
snapshot of data.

❌ Violation scenario — two dispatchers assign same vehicle

-- Transaction 1 reads Vehicle #5 — Status: Available
-- Transaction 2 reads Vehicle #5 — Status: Available
-- Both assign Vehicle #5 to different bookings 💥
-- Same vehicle now has two active bookings

D — Durability
Once transaction is committed, data is permanently saved — survives crashes, power failures,
restarts.

❌ Violation scenario:
-- Customer completes payment — transaction committed ✅
-- Server crashes immediately after commit
-- After restart — payment record is gone 💥
-- Customer charged but no order exists

-- ✅ Durability guaranteed via transaction log

-- SQL Server writes to transaction log BEFORE confirming commit
-- On restart — log replayed — committed data restored ✅

Confidential — For Interview Preparation Only 113 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What are the four SQL Server isolation levels? What read anomalies does
Q93 Hard
each prevent?
Ans Read Anomalies
Before isolation levels — three problems can occur when transactions run concurrently.

Dirty Read — Reading uncommitted data from another transaction

Non-Repeatable Read — Same row read twice gives different values
Phantom Read — Same query run twice returns different rows

1 — READ UNCOMMITTED — No Protection

Lowest isolation — transactions can read uncommitted changes from other transactions. Fastest but
most dangerous.

2 — READ COMMITTED — Default In SQL Server

Cannot read uncommitted data — waits for other transaction to commit first. Default isolation level.

3 — REPEATABLE READ — Locks Read Rows

Locks every row it reads — same row read twice always returns same value within transaction.

4 — SERIALIZABLE — Full Protection

Highest isolation — transactions run as if completely sequential. Locks range of rows — no new rows
can be inserted matching query conditions.

Confidential — For Interview Preparation Only 114 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Quick Reference
Dirty Read Non-Repeatable Phantom
READ UNCOMMITTED ❌ ❌ ❌
READ COMMITTED ✅ ❌ ❌
REPEATABLE READ ✅ ✅ ❌
SERIALIZABLE ✅ ✅ ✅
SNAPSHOT ✅ ✅ ✅

What is a deadlock? Explain the classic deadlock scenario and how SQL
Q94 Hard
Server detects and resolves it.
Ans Deadlock
A deadlock happens when two transactions are waiting for each other to release locks — neither
can proceed. SQL Server detects this and forcefully terminates one transaction to break the cycle.

Classic Deadlock Scenario

How SQL Server Detects It

SQL Server runs a background deadlock monitor that checks for circular lock dependencies every 5
seconds. When detected it picks one transaction as the deadlock victim — rolls it back and raises
error 1205.

 Deadlock victim receives this error

 Error 1205: Transaction was deadlocked on resources

Confidential — For Interview Preparation Only 115 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

 with another process and has been chosen as the deadlock victim

How To Prevent Deadlocks

What is SNAPSHOT isolation? How does it differ from READ COMMITTED

Q95 Hard
SNAPSHOT ISOLATION (RCSI)?
Ans What Is SNAPSHOT Isolation
SNAPSHOT isolation gives each transaction a consistent point-in-time view of the database using
row versioning — readers never block writers, writers never block readers. SQL Server stores old row
versions in tempdb.

What Is RCSI
READ COMMITTED SNAPSHOT ISOLATION is a database level setting that automatically upgrades
all READ COMMITTED statements to use row versioning — no code change needed in application.

Confidential — For Interview Preparation Only 116 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Key Difference
SNAPSHOT gives a consistent view from transaction start — reads same data throughout entire
transaction even if others commit changes. RCSI gives latest committed version at statement start —
each statement sees latest committed data.

What is a dirty read, non-repeatable read, and phantom read? Which

Q96 Hard
isolation level prevents each?
Ans Dirty Read
Reading uncommitted data from another transaction that might still be rolled back — you read data
that never officially existed.

Non-Repeatable Read
Reading the same row twice in one transaction gives different results — another transaction modified
it between your two reads.

Confidential — For Interview Preparation Only 117 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Phantom Read
Running the same query twice returns different rows — another transaction inserted or deleted rows
matching your filter between reads.

Which Isolation Level Prevents Each

Dirty Read Non-Repeatable Phantom
READ UNCOMMITTED ❌ ❌ ❌
READ COMMITTED ✅ ❌ ❌
REPEATABLE READ ✅ ✅ ❌
SERIALIZABLE ✅ ✅ ✅
SNAPSHOT ✅ ✅ ✅

Explain shared lock (S), exclusive lock (X), and update lock (U). What is
Q97 Hard
lock escalation?
Ans Shared Lock (S) — For Reading
Acquired when a transaction reads data. Multiple transactions can hold shared locks on the same row
simultaneously — reads never block each other. But a shared lock blocks exclusive locks.

Exclusive Lock (X) — For Writing

Confidential — For Interview Preparation Only 118 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Acquired when a transaction modifies data. Only one transaction can hold an exclusive lock — blocks
all other reads and writes until committed.

Update Lock (U) — Prevents Deadlocks

Acquired before a transaction intends to update. Only one transaction can hold a U lock — prevents
the classic deadlock pattern where two transactions both read then try to upgrade to exclusive.

Lock Escalation
When a transaction acquires too many row level locks, SQL Server automatically escalates to a
single table level lock — reduces memory overhead but blocks all other transactions on that table.

Confidential — For Interview Preparation Only 119 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What is the difference between NOLOCK hint and READ UNCOMMITTED
Q98 Hard
isolation? Why is NOLOCK dangerous in production?
Ans What Is NOLOCK Hint
NOLOCK is a table hint that tells SQL Server to read data without acquiring any shared locks —
equivalent to READ UNCOMMITTED isolation but applied to a specific table in a query.

Key Difference
NOLOCK is per table — other tables in same query still acquire locks. READ UNCOMMITTED applies
to every table in the entire transaction.

Why NOLOCK Is Dangerous In Production

Problem 1 — Dirty Reads

Problem 2 — Phantom And Duplicate Rows

Problem 3 — Wrong Business Decisions

Confidential — For Interview Preparation Only 120 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

▶ 5.4 Stored Procedures, Functions & Advanced

# Question Level
What is the difference between a Stored Procedure and a User-Defined
Q99 Medium
Function? What can an SP do that a UDF cannot?
Ans A Stored Procedure is a saved batch of SQL statements that can do almost anything — modify data,
call other procedures, handle transactions. A UDF is a reusable function that returns a value or table
but has strict limitations on what it can do."

Stored Procedure — Full Power

User Defined Function — Returns Value Only

What SP Can Do That UDF Cannot

Confidential — For Interview Preparation Only 121 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Key Difference
Stored Procedure UDF
Modify data ✅ Yes ❌ No
Transactions ✅ Yes ❌ No
Use in SELECT ❌ No ✅ Yes
Call SP ✅ Yes ❌ No
Multiple results ✅ Yes ❌ No

What is parameter sniffing? How does it cause production outages and

Q100 Hard
how do you mitigate it?
Ans Parameter Sniffing
When SQL Server first executes a stored procedure, it compiles an execution plan based on the first
parameter values passed. It caches that plan and reuses it for all future calls — even when different
parameter values would need a completely different plan.

How It Causes Problems

Confidential — For Interview Preparation Only 122 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

How To Detect It

Mitigation 1 — OPTIMIZE FOR UNKNOWN

Tell SQL Server to compile a plan based on average statistics — not the first parameter value.

Mitigation 2 — WITH RECOMPILE

Force SQL Server to recompile plan every execution — always uses actual parameter values.

Mitigation 3 — Local Variables

Confidential — For Interview Preparation Only 123 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Copy parameter into local variable — SQL Server compiles plan based on statistics not parameter
value.

Mitigation 4 — Clear Cached Plan

Quick fix for immediate production outage — flush the cached bad plan.

What are the dangers of using dynamic SQL in stored procedures? How
Q101 Hard
do you prevent SQL injection in T-SQL?
Ans What Is Dynamic SQL
Dynamic SQL is SQL built as a string at runtime and executed with EXEC or sp_executesql. Used
when table names, column names, or conditions are unknown at compile time.

The Danger — SQL Injection

 Attacker passes this as @Status:

 ' OR 1=1; DROP TABLE Orders; --
 Built query becomes:
 SELECT * FROM Orders WHERE Status = '' OR 1=1; DROP TABLE Orders; --
 Entire table dropped!

Prevention 1 — sp_executesql With Parameters

Always use sp_executesql with parameterized values — user input never becomes part of SQL
string.

Confidential — For Interview Preparation Only 124 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Prevention 2 — QUOTENAME For Dynamic Object Names

When table or column names are dynamic — use QUOTENAME to safely escape them.

Prevention 3 — Whitelist Valid Values

For column names or table names — validate against known safe values before using.

Prevention 4 — Least Privilege

Database user executing dynamic SQL should have minimum required permissions — even if
injection succeeds, damage is limited.

Explain the difference between Scalar UDFs and Table-Valued Functions.

Q102 Hard
Why do Scalar UDFs kill performance in SELECT queries?

Confidential — For Interview Preparation Only 125 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Ans Scalar UDF — Returns Single Value
A scalar UDF returns one single value. Simple to write but has a serious hidden performance
problem when used in SELECT or WHERE on large tables.

Table-Valued Function — Returns A Table

TVF returns a full result set — works set-based like a regular query. SQL Server can optimize,
parallelize, and join it efficiently.

Why Scalar UDFs Kill Performance

SQL Server executes scalar UDFs row by row — called once per row, cannot be parallelized, hides
its cost from execution plan. On large tables this is catastrophic.

Confidential — For Interview Preparation Only 126 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

What is a trigger? What are AFTER triggers vs INSTEAD OF triggers? Why

Q103 Medium
are triggers often considered anti-patterns?
Ans What Is A Trigger
A trigger is a special stored procedure that automatically executes when an INSERT, UPDATE, or
DELETE happens on a table. You cannot call it manually — SQL Server fires it automatically.

AFTER Trigger — Runs After The Operation

INSTEAD OF Trigger — Replaces The Operation

Why Triggers Are Considered Anti-Patterns

Confidential — For Interview Preparation Only 127 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Problem 1 — Hidden Behavior
 Developer runs simple insert
INSERT INTO Orders VALUES (1, 500, 'Pending')
 Trigger fires silently — sends email, updates 3 tables, calls API
 Nobody knows unless they check triggers

Problem 2 — Hard To Debug

 Error thrown inside trigger
 Stack trace points to INSERT statement — not the real problem
 Developer debugs wrong place for hours

Problem 3 — Performance Impact

 Bulk insert 100,000 rows
INSERT INTO Orders SELECT * FROM StagingOrders
 Trigger fires 100,000 times — unexpected slowdown

Problem 4 — Cascading Triggers

 Trigger A fires → updates Table B
 Trigger B fires → updates Table C
 Trigger C fires → updates Table A
 Infinite loop — stack overflow!

What is TempDB? Explain #temp tables vs ##global temp tables vs table

Q104 Hard
variables. When does each spill to TempDB?
Ans What Is TempDB
TempDB is a system database in SQL Server that resets completely on every restart. It stores
temporary objects — temp tables, table variables, row versions, sort operations, and hash joins.

#Temp Table — Session Scoped

Exists for the duration of the session or procedure that created it. Only visible to current session —
other sessions cannot see it.

Confidential — For Interview Preparation Only 128 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
##Global Temp Table — All Sessions
Visible to all sessions — exists until the creating session closes and no other session is using it.

Table Variable — Memory First

Declared like a variable — scoped to the batch or procedure. Lighter than temp tables — no DDL
transactions, minimal logging.

When Each Spills To TempDB

Table variables start in memory but spill to TempDB when data grows large. Temp tables always go
directly to TempDB.

 #Temp table — always TempDB, has statistics, good for large data
 Table variable — memory first, spills to TempDB when large
 no statistics — optimizer assumes 1 row always
 ##Global temp — always TempDB, visible everywhere

Key Comparison
#Temp ##Global Temp Table Variable
Scope Current session All sessions Current batch
Statistics ✅ Yes ✅ Yes ❌ No
Large data ✅ Good ✅ Good ❌ Poor
Transactions ✅ Yes ✅ Yes ❌ No
Best for Intermediate results Cross session sharing Small datasets

Confidential — For Interview Preparation Only 129 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What is the difference between DELETE, TRUNCATE, and DROP? Which
Q105 Easy
writes to transaction log minimally?
Ans DELETE — Row By Row Removal
DELETE removes rows one by one, logs every single row deletion in transaction log, fires triggers,
and can be rolled back. Slowest but most flexible.

TRUNCATE — Fast Full Table Clear

TRUNCATE removes all rows at once — logs only page deallocations not individual rows. Much
faster than DELETE but cannot filter rows and resets identity columns."

DROP — Removes Entire Table

DROP removes the entire table structure — data, indexes, constraints, triggers all gone. Cannot be
rolled back in most scenarios.

Confidential — For Interview Preparation Only 130 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Which Writes To Transaction Log Minimally
TRUNCATE writes minimally to transaction log — only logs page deallocations. DELETE logs every
single row. DROP logs table metadata removal.

Key Comparison
DELETE TRUNCATE DROP
Removes Specific or all rows All rows Entire table
Transaction log Full — every row Minimal — pages only Metadata only
Triggers ✅ Fires ❌ No ❌ No
Rollback ✅ Yes ✅ Yes ⚠️ Difficult
WHERE clause ✅ Yes ❌ No ❌ No
Resets identity ❌ No ✅ Yes N/A
Foreign keys ✅ Allowed ❌ Blocked ⚠️ Must drop FK first

What are schemas in SQL Server? How do they help with multi-tenancy and
Q106 Medium
security?
Ans Schema
A schema is a logical namespace inside a database that groups related objects — tables, views,
procedures — together. Default schema in SQL Server is dbo.

Creating And Using Schemas

How Schemas Help With Security

Grant or deny permissions at schema level — one permission covers all objects inside. No need to
grant permissions table by table.

Confidential — For Interview Preparation Only 131 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

How Schemas Help With Multi-Tenancy

Each tenant gets their own schema — complete data isolation without separate databases. Same
database, completely separated data.

Schema Based Routing In Application

What is Row-Level Security in SQL Server? How would you implement it

Q107 Hard
for a multi-tenant SaaS application?
Ans What Is Row Level Security

Confidential — For Interview Preparation Only 132 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Row Level Security automatically filters rows based on who is executing the query. Users only see
rows they are allowed to see — enforced at database level, invisible to application code.

How It Works
RLS uses a Security Policy with a predicate function. Every query against the table automatically has
the filter applied — application cannot bypass it.

Step By Step Implementation

Step 1 — Add TenantId To Tables

Step 2 — Create Filter Predicate Function

Step 3 — Create Security Policy

Step 4 — Set TenantId In Application Per Request

Confidential — For Interview Preparation Only 133 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

How It Works At Query Time

Block Predicate — Prevent Wrong Inserts

SECTION 6 — SYSTEM DESIGN & ARCHITECTURE

Confidential — For Interview Preparation Only 134 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
What is Clean Architecture? Explain the four layers and the Dependency
Q108 Hard
Rule.
Ans Clean Architecture
Clean Architecture organizes code into concentric layers where dependencies only point inward.
Inner layers know nothing about outer layers — business logic is completely isolated from frameworks,
databases, and UI.

The Four Layers

1 — Domain Layer (Innermost)

The heart of the application — pure business entities and rules. No dependencies on anything —
no EF Core, no [Link], nothing external.

2 — Application Layer
Orchestrates business use cases — what the application can do. Defines interfaces for
infrastructure. Depends only on Domain layer.

Confidential — For Interview Preparation Only 135 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

3 — Infrastructure Layer
Implements interfaces defined in Application layer — database, email, file storage, external APIs.
Depends on Application and Domain layers.

4 — Presentation Layer (Outermost)

Delivers the application to the outside world — API controllers, UI, CLI. Depends on Application layer
— never on Infrastructure directly.

Confidential — For Interview Preparation Only 136 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

The Dependency Rule

Dependencies always point inward only. Outer layers know about inner layers — inner layers never
know about outer layers. Domain knows nothing. Application knows Domain. Infrastructure knows
Application and Domain. Presentation knows Application.

Presentation → Application → Domain

Infrastructure → Application → Domain

Domain — knows NOTHING

Application — knows Domain only
Infrastructure — knows Application + Domain
Presentation — knows Application only

What is CQRS? What problem does it solve and what complexity does it
Q109 Hard
introduce?
Ans CQRS
CQRS stands for Command Query Responsibility Segregation — it separates read operations
(Queries) from write operations (Commands) into completely different models. One model optimized
for writing, another optimized for reading.

The Problem It Solves

In a single model, read and write requirements constantly conflict. Writes need validation, business
rules, domain logic. Reads need flat, fast, joined data for display. One model trying to serve both
becomes bloated and slow.

Confidential — For Interview Preparation Only 137 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Command Side — Write Model

Commands represent intent to change state. They go through full validation, business rules, and
domain logic. Return nothing or minimal confirmation.

Query Side — Read Model

Queries return flat optimized data for display — no domain logic, no business rules. Can bypass
domain entirely and hit database directly.

Confidential — For Interview Preparation Only 138 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

What Complexity It Introduces

Solves Introduces
─────────────────────────────────────────────────
Read/write model conflicts More classes and handlers
Slow queries from bloated model Separate read/write pipelines
Poor scalability Eventual consistency if separate DBs
Unclear responsibilities Steeper learning curve for team

What is the Repository pattern? When does it add value and when is it an
Q110 Medium
over-abstraction over EF Core?
Ans Repository Pattern
Repository pattern abstracts data access behind an interface — business logic talks to a repository
interface, never directly to EF Core or any database technology.

Confidential — For Interview Preparation Only 139 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

When It Adds Value

1 — Testability

2 — Swap Database Technology

3 — Clean Architecture Dependency Rule

Confidential — For Interview Preparation Only 140 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

When It Is Over-Abstraction
EF Core DbContext is already a repository and unit of work. Wrapping it in another repository layer
often just duplicates what EF Core already provides.

How would you design a multi-tenant SaaS database? Compare separate

Q111 Hard
databases vs shared schema with TenantId.
Ans Approach 1 — Separate Database Per Tenant
Each tenant gets their own isolated database. Complete data separation — one tenant can never
see another's data.

Confidential — For Interview Preparation Only 141 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Pros and Cons

✅ Complete data isolation
✅ Per tenant backup and restore
✅ Per tenant scaling
✅ Compliance friendly — GDPR, data residency
❌ Expensive — hundreds of databases
❌ Schema migrations run on every database
❌ Hard to manage at scale

Approach 2 — Shared Database With TenantId

All tenants share one database — every table has a TenantId column that isolates data at row level.

Confidential — For Interview Preparation Only 142 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Pros and Cons
✅ Cost effective — one database
✅ Simple migrations — one schema update
✅ Easy to manage at scale
❌ Noisy neighbor — one tenant affects others
❌ Accidental data leak risk if filter missed
❌ Harder to isolate per tenant backup

Approach 3 — Hybrid (Best Of Both)

Small tenants share one database. Large enterprise tenants get dedicated databases. Balance cost
and isolation.

When To Choose Which

Separate DB Shared DB Hybrid
Isolation needed ✅ Maximum Moderate Flexible
Cost ❌ High ✅ Low Moderate
Scale ❌ Hard ✅ Easy ✅ Balanced
Compliance ✅ Best Harder ✅ Good
Best for Enterprise SaaS Startup SaaS Growing SaaS

What is the Outbox pattern? How does it solve the dual-write problem in
Q112 Hard
distributed systems?
Ans Outbox Pattern
Instead of publishing directly to message bus, save the event to an Outbox table in the same
database transaction as your business data. A background job then reads the outbox and publishes
events reliably.

Step 1 — Outbox Table

Confidential — For Interview Preparation Only 143 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Step 2 — Save Business Data And Event Together

Step 3 — Background Publisher

Confidential — For Interview Preparation Only 144 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Dual Write Problem
When your application needs to save to database AND publish an event — both must succeed or both
must fail. Without a pattern, one can succeed while the other fails — data and events go out of sync.

How It Solves Dual Write

Before Outbox
Save Order → DB Publish Event → Crash = inconsistency

After Outbox
Save Order + Save OutboxMessage → Same Transaction → Atomic
Background Job → Reads Outbox → Publishes Event → Marks Processed
Crash between publish and mark? → Job retries → Event published again

Explain Circuit Breaker and Retry patterns. How would you implement
Q113 Hard
them using Polly in .NET?
Ans Circuit Breaker Pattern
After too many consecutive failures, stop trying and fail fast for a period. Prevents hammering a
struggling service — gives it time to recover.

CLOSED → Normal operation — requests flow through

OPEN → Too many failures — requests fail immediately, no calls made
HALF-OPEN → After timeout — test one request — if success go CLOSED, if fail stay OPEN

Confidential — For Interview Preparation Only 145 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Retry Pattern
When a call fails due to a transient error — network blip, temporary timeout — retry automatically a
few times before giving up. Not all failures are permanent.

Combine Both — Retry Then Circuit Breaker

How do you approach database pagination for a table with 50 million

Q114 Hard
rows? Compare OFFSET/FETCH vs keyset pagination.
Ans Fetching page 5000 of results from a 50 million row table using OFFSET means SQL Server skips
250,000 rows every time — scanning and discarding them. Gets slower the deeper you paginate.

OFFSET/FETCH — Simple But Slow Deep Pages

Confidential — For Interview Preparation Only 146 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Problems with OFFSET:

❌ Gets slower as page number increases
❌ Inconsistent results — new rows inserted shift pages
❌ Cannot scale to deep pages on large tables

Keyset Pagination — Fast At Any Depth

Instead of skipping rows, use the last seen value as a bookmark — SQL Server seeks directly to that
position using an index. Same speed on page 1 or page 50,000.

Confidential — For Interview Preparation Only 147 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Required Index For Keyset Performance

Comparison
OFFSET/FETCH Keyset
Deep page performance ❌ Degrades badly ✅ Constant speed
Random page access ✅ Jump to any page ❌ Must go sequentially
Implementation ✅ Simple Moderate
Consistent results ❌ Rows can shift ✅ Stable
Best for Small tables, few pages Large tables, infinite scroll

What is an API Gateway? When would you introduce one and what are
Q115 Medium
the downsides?
Ans API Gateway
An API Gateway is a single entry point that sits in front of all your backend services. Clients talk to
the gateway — the gateway routes requests to the right service, handling cross cutting concerns
centrally.

Confidential — For Interview Preparation Only 148 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Client (Mobile/Web)
↓
API Gateway
↙ ↓ ↘
Order Payment User
Service Service Service

What It Handles Centrally

✅ Authentication & Authorization — verify token once, not in every service
✅ Rate Limiting — throttle requests per client
✅ Request Routing — forward to correct microservice
✅ Load Balancing — distribute traffic across instances
✅ SSL Termination — HTTPS handled at gateway
✅ Logging & Monitoring — one place to observe all traffic
✅ Request Aggregation — combine multiple service calls into one response

When To Introduce One

✅ Multiple microservices — clients need one address
✅ Cross cutting concerns duplicated across services
✅ Mobile clients — aggregate multiple calls into one round trip
✅ Need to expose different APIs for different clients
(mobile gets simplified API, partners get full API)

Popular Options In .NET Ecosystem

Simple YARP Configuration

Confidential — For Interview Preparation Only 149 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Downsides
❌ Single point of failure — gateway goes down, everything goes down
❌ Added latency — extra network hop on every request
❌ Operational complexity — another service to deploy, monitor, scale
❌ Can become a bottleneck — all traffic flows through one place
❌ Over-centralisation — teams depend on gateway team for changes

How do you cache data in [Link] Core? Compare IMemoryCache,

Q116 Medium
IDistributedCache, and output caching.
Ans Cache Data In [Link] Core
[Link] Core provides three built-in caching mechanisms — IMemoryCache for in-process caching,
IDistributedCache for shared caching across servers, and Output Caching for caching entire HTTP
responses.

IMemoryCache — Single Server Cache

Stores data in application memory. Fastest but not shared across multiple servers — each server has
its own cache.

IDistributedCache — Shared Cache Across Servers

Confidential — For Interview Preparation Only 150 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Stores data in external Redis or SQL Server — all server instances share same cache. Slower than
memory cache due to network hop.

Output Caching — Cache Entire HTTP Response

Caches the complete HTTP response — request never reaches controller. No business logic changes
needed.

How would you implement background job processing in .NET?

Q117 Hard
Compare IHostedService, Worker Service, and Hangfire.
Ans Background Job Processing In .NET
[Link] Core provides built-in support for background processing through IHostedService and
BackgroundService. For more advanced scenarios like persistent, scheduled, or retriable jobs — use
a library like Hangfire.

IHostedService — Simple Background Task

IHostedService is the base interface for running background work in [Link] Core. Starts when
application starts, stops when application stops.

Confidential — For Interview Preparation Only 151 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

BackgroundService — Cleaner IHostedService

BackgroundService is an abstract class built on IHostedService — cleaner pattern, just override
ExecuteAsync.

Worker Service — Standalone Background App

Worker Service is a separate .NET project — runs as a Windows Service or Linux daemon
independently from the API. Best for heavy background processing that should not share resources
with API.

Confidential — For Interview Preparation Only 152 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

Hangfire — Full Featured Job Scheduler

Hangfire provides persistent job scheduling with a dashboard — fire and forget, delayed, recurring,
and continuation jobs. Jobs survive application restarts.

Comparison
IHostedService Worker Service Hangfire
Persistent jobs ❌ No ❌ No ✅ Yes
Dashboard ❌ No ❌ No ✅ Yes
Survives restart ❌ No ❌ No ✅ Yes
Scheduling Manual Manual ✅ Built in
Best for Simple polling Heavy isolated work Production job scheduling

Confidential — For Interview Preparation Only 153 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

SECTION 7 — SCENARIO & BEHAVIORAL QUESTIONS

📌 These questions assess problem-solving thinking. Use STAR format: Situation → Task → Action →
Result. Be specific — real numbers and outcomes are far more convincing than vague generalities.

# Question Level
Tell me about a time a production API was slow. Walk me through how
Q118 Hard
you diagnosed and resolved it.
Ans In my current role at [Your Company Name], we had a situation where our [You API/Project Name]
API was returning [List Nmae] lists very slowly — response times were around 4 to 5 seconds on the
booking history endpoint. Users were complaining and the client escalated it.

My first step was to reproduce the issue locally with similar data volume. I attached SQL Server
Profiler to capture the actual queries being generated by EF Core. What I found was that the endpoint
was loading bookings along with vehicle details, driver details, and customer details — but each
related entity was being loaded separately. It was a classic N+1 problem. For 100 bookings we were
making 300 additional database calls.

The root cause was that the developer who wrote that endpoint used lazy loading and never included
the related entities explicitly. So every time the code accessed a navigation property, EF Core silently
fired another query.

My fix was straightforward — I replaced the lazy loading with eager loading using Include and
ThenInclude, and I also added a projection to return only the columns the frontend actually needed
instead of loading entire entities. I also added a composite index on the BookingDate and CustomerId
columns which were used in the WHERE and ORDER BY clauses.

After deploying the fix, response time dropped from 4 seconds to under 200 milliseconds. I also took it
as an opportunity to disable lazy loading globally in our DbContext to prevent the same mistake
happening again on other endpoints.

The lesson I took from this was — always profile your queries in a realistic data environment early.
Problems like N+1 are invisible in development with 10 rows but catastrophic in production with
thousands.

You are building an API that must handle 10,000 requests per second.
Q119 Hard
What are the first five bottlenecks you would investigate?
Ans When I think about handling 10,000 requests per second, my approach would be systematic —
starting from where the request enters the system and following it all the way to the database and
back.

The first thing I would investigate is the database. In almost every high traffic system, the
database is the bottleneck. I would look at slow queries, missing indexes, N+1 problems, and
connection pool exhaustion. At that scale even a 50 millisecond query running 10,000 times per
second becomes catastrophic.

The second thing I would look at is caching. If the same data is being fetched from the database
on every request that is unnecessary load. I would identify data that is read frequently but changes
rarely — reference data, configurations, lookup tables — and cache it in Redis or IMemoryCache.
Reducing database hits by even 60 percent makes a massive difference at that scale.

Confidential — For Interview Preparation Only 154 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Third I would investigate thread pool exhaustion. At 10,000 requests per second, if any code path
is blocking — using dot Result or dot Wait on async methods — threads get exhausted and new
requests start queuing. I would ensure the entire call chain is truly async from controller all the way
down to the database.

Fourth I would look at unnecessary memory allocations and garbage collection pressure.
Frequent Gen 2 collections pause the entire application. At high throughput, small inefficiencies in hot
paths add up quickly. I would use dotMemory or Visual Studio diagnostics to identify allocation
hotspots.

Fifth I would investigate the infrastructure itself — whether the API is horizontally scaled behind a
load balancer, whether read replicas exist for read heavy workloads, and whether connection pool
sizes are tuned for the expected load.
The honest answer is you cannot know the real bottleneck until you measure — so alongside
everything else I would set up Application Insights from day one to see exactly where time is being
spent on every single request.

A SQL query that took 200ms in development takes 45 seconds in

Q120 Hard
production. Walk me through your debugging process step by step.
Ans This is actually a very realistic scenario and I have faced something similar. My debugging process
would be very systematic.

The first thing I would do is check if it is a data volume problem. Development databases
typically have hundreds or thousands of rows. Production has millions. A query without proper indexes
that runs fine on small data degrades badly at scale. I would immediately check row counts on the
tables involved.

Second I would capture the actual query being executed in production. Sometimes ORM
generated queries look different than expected — EF Core might be generating a cartesian product,
loading unnecessary columns, or missing a filter. I would use SQL Server Profiler or Extended Events
to capture the exact SQL hitting the database.

Third I would look at the execution plan in production. This is the most important step. I would run
the query in SSMS with actual execution plan enabled and look for Table Scans, Key Lookups, Hash
Joins on large tables, and Sort operators with high cost percentage. The execution plan tells me
exactly where SQL Server is struggling.

Fourth I would check for parameter sniffing. A 200ms query in development with small parameter
values can become 45 seconds in production if SQL Server cached a plan based on a parameter that
returns very few rows but the production call returns millions. I would check plan cache and try running
the query with OPTION RECOMPILE to see if the plan changes.

Fifth I would check index fragmentation and statistics. Stale statistics cause SQL Server to make
wrong row estimates and choose terrible execution plans. I would run UPDATE STATISTICS and
check fragmentation levels on relevant indexes.

Sixth I would check for blocking and locking. In production with concurrent users, the query might
not actually be slow — it might be waiting for locks held by another transaction. I would check
sys.dm_exec_requests for blocking chains.

The most important mindset here is — never assume. Every step is about gathering evidence before
making a change. A 45 second query always has a reason and the execution plan almost always
points directly to it.

Confidential — For Interview Preparation Only 155 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level

How would you refactor a 3,000-line God Controller into Clean

Q121 Hard
Architecture without breaking existing functionality?
Ans This is a very common real world problem and the key is doing it safely and incrementally — never a
big bang rewrite.

The first thing I would do is understand what is in there before touching anything. I would read
through the entire controller, document every endpoint, what it does, what tables it touches, what
business rules it enforces. You cannot refactor what you do not fully understand. A 3,000 line
controller usually has hidden business logic, special cases, and silent dependencies that are not
obvious at first glance.

Second I would make sure there are integration tests covering the existing endpoints before
changing a single line. If tests do not exist I would write them first — testing the HTTP responses,
not the implementation. These tests become my safety net. If anything breaks during refactoring, the
tests catch it immediately.

Third I would start extracting without changing behavior — not redesigning. My first move is
purely mechanical — take database calls out of the controller and put them in a repository. Take
business logic out and put it in a service class. The controller still calls everything the same way,
results are identical, but code is now in the right place. This is called the Strangler Fig pattern —
gradually replace pieces without disrupting the whole.

Fourth I would introduce the Application layer use cases one endpoint at a time. Not all 50
endpoints at once — pick the simplest one, extract it fully into a command or query handler, wire it up,
run the tests, deploy. Then move to the next one. Each deployment is small and low risk.

Fifth I would move infrastructure concerns to the Infrastructure layer — DbContext calls behind
repository interfaces, external HTTP calls behind service interfaces. This is when the code becomes
truly testable because dependencies can be mocked.

The most important principle throughout is — never refactor and add features at the same
time. Refactoring commits and feature commits stay completely separate. This way if something
breaks you know exactly what caused it.

In my experience the biggest mistake teams make is trying to rewrite everything perfectly in one sprint.
That always ends in broken functionality and rollbacks. Small safe incremental steps with tests running
after every change is the only approach that works reliably in production systems.

You discover a critical SQL injection vulnerability in a live system. What

Q122 Hard
are your next steps?
Ans This is a serious situation and the response needs to be both fast and controlled. I would treat it as an
incident and follow a clear priority order.

The very first thing I would do is notify my team lead and management immediately. This is not
something to handle alone or quietly. A SQL injection vulnerability in a live system is a security
incident — the right people need to know right away so decisions can be made at the right level.

Second I would assess the blast radius before touching anything. I would try to understand —
has this vulnerability already been exploited? I would check application logs, database audit logs, and
query history for suspicious patterns like unusual SELECT statements, unexpected data dumps, or

Confidential — For Interview Preparation Only 156 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
DROP and DELETE commands that should not be there. Knowing whether data was already
compromised changes everything about the response.

Third I would apply an immediate temporary mitigation. If the vulnerable endpoint is identifiable I
would either take it offline temporarily, add a WAF rule to block suspicious input patterns, or restrict
access to that endpoint until a proper fix is deployed. Stopping the bleeding comes before writing the
fix.

Fourth I would write and deploy the actual fix as fast as possible. Replace string concatenated
queries with parameterized queries or stored procedures with sp_executesql. This fix is usually small
and surgical — a few lines changed — but it needs proper code review even under pressure. A rushed
fix that introduces another bug is worse than the original problem.

Fifth I would do a full codebase audit. One SQL injection usually means the same pattern exists
elsewhere. I would search the entire codebase for string concatenation with user input in database
queries and fix every instance, not just the one reported.

Sixth if any user data was potentially exposed I would follow the data breach notification
process — which depending on the region and regulations may legally require notifying affected
users and relevant authorities within a specific timeframe.

The lesson I would take forward is adding automated security scanning to the CI/CD pipeline so
vulnerabilities like this get caught before they ever reach production. Prevention is always cheaper
than incident response.

A third-party API your system depends on starts returning 429 (Too Many
Q123 Medium
Requests). How do you handle this gracefully?
Ans A 429 is actually one of the more manageable failures because the third party is telling you exactly
what is wrong — you are sending too many requests. My response would be layered.

The first thing I would do is read the response headers. Most APIs that return 429 include a Retry-
After header telling you exactly how long to wait before retrying. That is the first thing to respect —
blindly retrying immediately just makes the problem worse and could get your API key blocked
entirely.

Second I would implement a retry policy with exponential backoff using Polly. Instead of retrying
immediately, wait progressively longer between attempts — 2 seconds, then 4, then 8. This gives the
third party service breathing room and dramatically improves the chance of eventual success without
hammering their servers.

Third I would implement a circuit breaker on top of the retry policy. If the 429s are sustained —
meaning the service is consistently rate limiting us — the circuit breaker opens and we stop making
calls entirely for a period. This protects both our thread pool and the third party service.

Fourth I would look at why we are hitting the rate limit in the first place. Are we making
redundant calls for the same data? If so, caching the responses is the right fix. If the same data is
requested frequently I would cache it for an appropriate duration so we only call the third party once
and serve the rest from cache.

Fifth I would implement a request queue for non urgent calls. Instead of calling the third party
synchronously on every user request, queue the work and process it at a controlled rate that respects
the API limits. This decouples our system from their rate limits entirely.

Confidential — For Interview Preparation Only 157 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
Finally I would add proper monitoring and alerting so we know immediately when 429s start
appearing — not when users start complaining. Seeing the pattern early gives us time to respond
before it becomes a user facing problem.

The broader lesson is that any external dependency is a risk. I would always design integrations with
the assumption that the third party can be slow, unavailable, or rate limited — and build resilience in
from the start rather than retrofitting it after an incident.

You need to add a new non-nullable column to a production table with 100
Q124 Hard
million rows. How do you do it without downtime?
Ans This is a classic database migration challenge and doing it wrong on a 100 million row table means
hours of table locks and complete downtime. The key is breaking it into safe incremental steps.

The first thing I would understand is that you cannot simply add a non-nullable column without
a default in one step on a table this size. SQL Server needs to update every single row — that
means a lock on the entire table for potentially hours. That is unacceptable in production.

My approach would be three separate deployment steps.

Step one — add the column as nullable first. Adding a nullable column to a large table in SQL
Server is a metadata only operation — it completes in milliseconds with no row updates and no table
lock. This is the safe first step.

Step two — backfill the data in small batches. Never update 100 million rows in one statement —
that creates a massive transaction, locks rows for a long time, and fills the transaction log. Instead
update in small batches of 1000 to 5000 rows at a time with a short delay between batches to let other
queries breathe.

Step three — once all rows are populated, add the NOT NULL constraint. In SQL Server 2012
and later, if you add a NOT NULL constraint with a default value it is also a metadata only operation —
no row updates needed.

On the application side I would also handle this carefully. During the migration period the column
is nullable — so the application code must handle null values gracefully. I would deploy the application
update that handles the nullable column first, run the migration, then deploy the final update that treats

Confidential — For Interview Preparation Only 158 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
it as non-nullable. This is the expand and contract pattern — expand the schema, migrate data,
contract to final state.

Finally I would run this entire process during low traffic hours even though it is designed to be
safe — there is no reason to add unnecessary risk during peak load.

The entire philosophy here is — never do in one step what can be done safely in three. On a table this
size patience and incrementalism is always the right approach.

Describe how you would approach migrating a monolith to microservices

Q125 Hard
incrementally using the Strangler Fig pattern.
Ans This is a question about architectural maturity and the key word is incrementally — the worst thing
you can do is attempt a big bang rewrite of a monolith into microservices. That almost always fails.

First I would challenge whether microservices are actually needed. A monolith is not inherently
bad. If the team is small, the domain is not that complex, or the scaling requirements do not justify the
operational overhead — I would keep the monolith and improve it instead. Microservices solve specific
problems but introduce real complexity. I would make sure the problems exist before applying the
solution.

Assuming microservices are justified, the Strangler Fig pattern works by building new
functionality around the outside of the monolith gradually — never touching the core until you
are ready to replace it. Just like the strangler fig tree grows around an existing tree until the original
tree is completely replaced.

The first concrete step is introducing an API Gateway in front of the monolith. All traffic still goes
to the monolith but now there is a routing layer I control. This is the foundation of the entire migration
— without it I cannot redirect traffic incrementally.

Second I would identify the best candidate for the first microservice. I would not start with the
most complex or most critical part of the system. I would look for a bounded context that is relatively
self contained, has clear boundaries, minimal database coupling with the rest of the monolith, and
ideally one that needs independent scaling or frequent deployment. Something like a notification
service or reporting service is a good first candidate.

Third I would extract that service without touching the monolith database immediately. The new
service gets its own codebase and its own deployment pipeline. Initially it might still share the monolith
database — that is acceptable as a temporary state. Database separation comes later. Getting the
service boundary right comes first.

Fourth I would use the API Gateway to redirect traffic for that specific bounded context to the
new service. The monolith still has the old code — I have not deleted anything yet. If the new service
has problems I flip the gateway back to the monolith in seconds. This is the safety net.

Fifth I would separate the database. Once the service is stable and proven I would migrate its data
to its own database. This is usually the hardest step — breaking shared database dependencies
requires careful data synchronization during the transition period. The Outbox pattern and event driven
communication help here to keep data consistent across the boundary.

Then I would repeat this cycle — identify next candidate, extract, redirect traffic, separate database,
validate, then remove the dead code from the monolith. Over time the monolith shrinks and the
strangler fig has fully replaced it.

Confidential — For Interview Preparation Only 159 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Question Level
The most important discipline throughout is — never extract and redesign at the same time.
When extracting a service the behavior must be identical to the monolith. Refactoring and migration in
the same step doubles the risk. Extract first, improve later.

In my honest opinion the teams that succeed at this migration are the ones that treat it as a year long
journey with small safe steps — not a three month sprint to rewrite everything.

SECTION 8 — QUICK-FIRE TRICKY QUESTIONS

⚡ These are common 'gotcha' questions interviewers use to test depth of knowledge. Each should be answered in
under 60 seconds.

# Quick-Fire Tricky Question Level

QA What is the output of: [Link](0.1 + 0.2 == 0.3); — and why? Medium
Ans The output is False.
The reason is floating point numbers cannot represent 0.1 and 0.2 exactly in binary. When added
together the result is 0.30000000000000004 — not exactly 0.3. So the comparison fails.
For precise comparisons use epsilon checking. For financial calculations always use decimal instead of
double — decimal uses base 10 and avoids this problem entirely.

Can you catch an exception from a Task that is not awaited? What happens
QB Hard
to it?
Ans No you cannot catch it — the exception is silently swallowed and your try catch is completely useless.

In older .NET versions unobserved task exceptions would crash the process. In .NET 4.5 and later they
are silently swallowed by default — which is actually more dangerous because you never know
something failed.

The fix is simple — always await your tasks. If you genuinely need fire and forget, at minimum attach a
continuation to log the exception.

QC Is [Link] == "" true? What about [Link]("")? Explain. Easy

Ans Both are True — and for the same reason.

[Link] is simply a static field that holds an empty string literal "". They are the same value, same
length, same content — zero characters.
Since C# overloads == for strings to compare by value not reference, both == and .Equals() compare
content — both see two empty strings and return true.
There is no practical difference between [Link] and "" — it is purely a style preference. Some
teams prefer [Link] because it makes the intent explicit and avoids any confusion about whether
the quotes contain a space.

Confidential — For Interview Preparation Only 160 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Quick-Fire Tricky Question Level

What does 'SELECT * FROM Orders WITH (NOLOCK)' actually return? Is it
QD Hard
safe?
Ans It returns rows without acquiring any shared locks — which sounds fast and safe but is actually
dangerous in production.

What it can actually return is dirty data — rows from uncommitted transactions that might get rolled back
and never officially existed. Even worse, due to page splits happening during the read, it can return the
same row twice or skip rows entirely. No exception thrown, no warning — just silently wrong data.

It is not safe for any data that needs to be accurate — financial figures, order statuses, inventory counts.
The only scenario where it is arguably acceptable is rough approximate reporting where a slightly wrong
count does not matter.
The correct alternative is enabling READ COMMITTED SNAPSHOT ISOLATION at the database level
— you get the same concurrency benefit with zero dirty read risk and accurate data every time.

In EF Core, if you call SaveChanges() inside a loop 1000 times vs once —

QE Hard
what is the difference?
Ans Massive performance difference.

Calling SaveChanges() inside a loop 1000 times means 1000 separate database round trips — each
one opens a connection, sends the command, waits for acknowledgment, closes. Under load this is
catastrophically slow.

EF Core tracks all changes in memory until SaveChanges is called — so adding everything first then
saving once wraps all 1000 inserts in a single transaction with one round trip. Dramatically faster and
more efficient.

For extremely large datasets even one SaveChanges can be slow — in that case batch in chunks of 500
or 1000 rows using EF Core bulk extensions.

What does 'yield return' do? What type does a method with 'yield return'
QF Medium
return?
Ans yield return turns a method into a lazy iterator — instead of building the entire collection in memory
and returning it all at once, it returns items one at a time as the caller requests them.

A method using yield return must return either IEnumerable<T>, IEnumerable,

IAsyncEnumerable<T>, or IEnumerator<T>.

The key benefit is memory efficiency — if you have a million records you do not load all million into
memory. You process one at a time. The compiler transforms the method into a state machine behind
the scenes — very similar to how async await works.

Can a struct implement an interface in C#? Can it inherit from another

QG Medium
struct?
Ans Yes a struct can implement an interface — and no, a struct cannot inherit from another struct.

The reason structs cannot inherit is that structs are value types — inheritance requires reference type
semantics and virtual dispatch which value types do not support. Structs implicitly inherit from
[Link] but you cannot extend that chain yourself.

Confidential — For Interview Preparation Only 161 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Quick-Fire Tricky Question Level

One important gotcha — when a struct is assigned to an interface variable it gets boxed onto the heap,
which defeats the performance benefit of using a struct in the first place.

QH What is the difference between [Link](1000) and [Link](1000)? Medium

Ans Both wait 1000 milliseconds but they do it completely differently.

[Link] blocks the current thread — the thread sits idle doing absolutely nothing for 1 second.
In a web API that means a thread pool thread is wasted just waiting.

[Link] releases the thread back to the thread pool during the wait. The thread goes off and
serves other requests. After 1 second a thread picks up where it left off. Zero threads wasted.

The rule is simple — in any async context never use [Link]. Always use await [Link].
[Link] is only acceptable in non async console apps or truly dedicated background threads
where blocking is intentional.

What SQL Server function would you use to find duplicate rows across
QI Medium
multiple columns?
Ans I would use GROUP BY with HAVING COUNT(*) > 1 — it groups rows by the columns you want to
check for duplicates and filters to only groups with more than one row.

If I also need to see the actual duplicate rows and their IDs — to delete them for example — I would use
ROW_NUMBER to identify which rows to keep and which to remove.

What is the difference between GETDATE() and GETUTCDATE()? Which

QJ Easy
should you use and why?
Ans GETDATE() returns the server's local time — whatever timezone the SQL Server machine is
configured to. GETUTCDATE() returns Coordinated Universal Time — timezone independent.

You should always use GETUTCDATE() in production systems — and here is why.

If your server is in Pakistan and your users are in UAE, London, and New York — storing local server
time creates confusion and incorrect time comparisons. If the server ever moves to a different region or
daylight saving time kicks in, all your historical timestamps are now inconsistent.

Confidential — For Interview Preparation Only 162 / 163

 .NET Backend & SQL Server — Interview Preparation Guide Muhammad Afzal | Full Stack .NET Developer

# Quick-Fire Tricky Question Level

UTC has no timezone, no daylight saving shifts, no ambiguity — it is the same moment in time
regardless of where the server or user is located.

Store UTC in the database, convert to the user's local timezone only at the presentation layer. This is
the industry standard approach for any system serving users across multiple timezones.

PREPARATION TIPS — FROM A SENIOR PERSPECTIVE

✅ DO ❌ DON'T
Think out loud — show reasoning, not just answers Memorise answers without understanding them
Say 'it depends' and then explain the tradeoffs Say you 'know' SOLID if you can't give code examples
Relate answers to AIBotNexa or real projects you built Forget to mention testing — mention unit tests naturally
Ask clarifying questions before answering system Skip drawing diagrams — visuals show structured
design thinking
Know your SQL execution plans — senior devs love Panic if you don't know — pivot to related knowledge
this Ignore SQL questions — they are heavily tested at all
Admit what you don't know — say how you'd learn it levels

Good Luck, for your next interview!

Confidential — For Interview Preparation Only 163 / 163