SQL Server, EF Core & Database Architecture Masterclass

Masterclass: SQL Server, EF Core & System Architecture (7-8 YOE Senior/Lead Prep)

Complete Senior/Lead Interview Prep (7-8 YOE): SQL Fundamentals, Joins, Window Functions, Index Tuning, Deadlocks, EF Core, REST API Scenarios & Architecture

1. SQL Basics & Core Data Types (From Zero to Senior)

Beginner to Intermediate Essentials

1.1 RDBMS Architecture: Database, Schema, Table, Row, Column

🏢 The Corporate Office Metaphor:
• Database Instance = The Corporate Headquarters Building.
• Database = A specific Department (e.g. Finance DB).
• Schema = A specific filing room inside that department (`sales`, `inventory`, `dbo`).
• Table = A physical Filing Cabinet holding invoices.
• Row (Record) = A single paper invoice sheet.
• Column (Field) = A specific entry field on that invoice (e.g. Invoice Date, Total Amount).

1.2 Data Types Matrix & Memory Storage Rules

Data Type Comparison	Storage Size & Behavior	When to Use in Real Project	When NOT to Use
`VARCHAR(N)` vs `NVARCHAR(N)`	`VARCHAR` uses 1 byte per char (ASCII). `NVARCHAR` uses 2 bytes per char (Unicode).	Use `NVARCHAR` for multi-language UTF-8 strings (names, addresses). Use `VARCHAR` for ASCII codes (`US`, `EUR`, `DEPT_1`).	Don't use `NVARCHAR` everywhere blindly—it doubles RAM/disk usage!
`CHAR(N)` vs `VARCHAR(N)`	`CHAR` is fixed-length (padded with spaces). `VARCHAR` is variable length.	Use `CHAR(2)` for fixed 2-letter Country Codes (`US`, `IN`, `UK`) or fixed 10-char PAN numbers.	Don't use `CHAR` for user emails or product names—wastes space!
`INT` vs `BIGINT`	`INT` is 4 bytes ($\le 2.14$ Billion). `BIGINT` is 8 bytes ($\le 9$ Quintillion).	Use `BIGINT` for high-volume transactions, audit logs, and order line items.	Don't use `BIGINT` for small lookup tables like Status codes (`1=Pending`, `2=Paid`). Use `TINYINT` (1 byte).
`DATETIME` vs `DATETIME2`	`DATETIME` (8 bytes, 3.33ms accuracy). `DATETIME2` (6-8 bytes, 100ns precision, wider range 0001-9999).	Always prefer `DATETIME2` in modern .NET SQL Server applications!	Avoid legacy `DATETIME` in new development.
`DECIMAL(18,2)` vs `FLOAT`	`DECIMAL` is exact fixed-point. `FLOAT` is approximate floating-point.	ALWAYS use `DECIMAL(18,2)` for Currency, Prices & Tax calculations!	NEVER use `FLOAT` for Financial Money data! Float causes rounding precision errors ($10.0000000000000002$)!

1.3 Keys & Constraints Matrix

CREATE TABLE Categories (
  Id INT PRIMARY KEY IDENTITY(1,1), -- Primary Key: Unique, Non-Null, Clustered Index
  CategoryCode VARCHAR(20) NOT NULL UNIQUE, -- Unique Constraint
  IsActive BIT NOT NULL DEFAULT 1 -- Default Constraint
);

CREATE TABLE Products (
  Id INT PRIMARY KEY IDENTITY(1,1),
  ProductName NVARCHAR(200) NOT NULL,
  UnitPrice DECIMAL(18,2) NOT NULL CHECK (UnitPrice >= 0), -- Check Constraint
  CategoryId INT NOT NULL,
  CONSTRAINT FK_Products_Categories FOREIGN KEY (CategoryId) 
    REFERENCES Categories(Id) ON DELETE RESTRICT -- Foreign Key
);

2. CRUD Operations & Safe Handling of NULLs

Core Operations

2.1 CRUD Syntax & The Danger of Unbounded Updates/Deletes

🚨 Production Disaster Scenario:
A junior developer runs UPDATE Products SET Price = 0 accidentally omitting the WHERE Id = 50 clause! In 1 second, all 500,000 products in the enterprise database are set to FREE! 💥

💡 Senior Safeguard Practice: Always run SELECT with the exact WHERE clause FIRST before executing UPDATE or DELETE, or wrap inside a transaction: BEGIN TRAN ... ROLLBACK.

2.2 NULL Handling: `COALESCE`, `NULLIF`, `CASE` Expressions

-- 1. COALESCE: Returns first NON-NULL value in list (Replaces ISNULL)
SELECT CustomerName, COALESCE(MobilePhone, HomePhone, WorkPhone, 'N/A') AS ContactPhone
FROM Customers;

-- 2. NULLIF: Returns NULL if two expressions are equal (Prevents Divide-By-Zero errors!)
SELECT ProductId, TotalRevenue / NULLIF(TotalQuantitySold, 0) AS AveragePricePerItem
FROM ProductSalesSummary;

-- 3. CASE Expression: Conditional business logic inside SQL queries
SELECT OrderId, TotalAmount,
  CASE 
    WHEN TotalAmount >= 100000 THEN 'PLATINUM'
    WHEN TotalAmount >= 50000  THEN 'GOLD'
    ELSE 'STANDARD'
  END AS CustomerTier
FROM Orders;

3. SQL Joins Deep Dive & Duplicate Row Fixes

Joins & Performance

3.1 Complete Join Hierarchy & Visual Logic

🔗 Customer ➡️ Orders ➡️ OrderItems ➡️ Product Relationship:
• INNER JOIN: Customers who HAVE placed at least 1 order.
• LEFT JOIN: ALL Customers (including those who have NEVER ordered anything yet).
• RIGHT JOIN: All Orders + matched Customer details.
• FULL OUTER JOIN: All Customers AND all Orders whether matched or unmatched.
• CROSS JOIN: Cartesian product of all Customers $\times$ all Products ($N \times M$).

3.2 Finding Records That Do NOT Exist in Another Table

Interview Question: How do you find Customers who have NEVER placed an Order?

-- Approach 1: LEFT JOIN with WHERE NULL check
SELECT c.Id, c.CustomerName 
FROM Customers c 
LEFT JOIN Orders o ON c.Id = o.CustomerId 
WHERE o.Id IS NULL;

-- Approach 2: NOT EXISTS (PREFERRED for performance in SQL Server!)
SELECT c.Id, c.CustomerName 
FROM Customers c 
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id
);

💡 Senior Interview Answer (`NOT EXISTS` vs `LEFT JOIN` vs `NOT IN`):
1. NOT EXISTS stops scanning the B-tree index the moment it finds the first match ($O(1)$ fast!).
2. NOT IN can produce ZERO RESULTS if the subquery returns even a single `NULL` value! (Top Senior Trap!). Always prefer `NOT EXISTS`.

4. Aggregation, GROUP BY & WHERE vs HAVING

Aggregation Masterclass

4.1 WHERE vs HAVING Comparison

Feature	WHERE Clause	HAVING Clause
Execution Order	Executes BEFORE data is grouped (`GROUP BY`).	Executes AFTER data is aggregated (`GROUP BY`).
Allowed Expressions	Filters individual raw rows (`WHERE UnitPrice > 100`).	Filters aggregated calculations (`HAVING SUM(Amount) > 100000`).
Index Usage	Uses B-tree indexes directly to filter rows scanned.	Filters results already kept in memory post-aggregation.

4.2 Conditional Aggregation (Real-World Enterprise Report Pattern)

-- Calculates Total Sales, Completed Sales, and Cancelled Sales in 1 Single Query:
SELECT 
  VendorId,
  COUNT(*) AS TotalOrders,
  SUM(CASE WHEN Status = 'Completed' THEN TotalAmount ELSE 0 END) AS CompletedRevenue,
  SUM(CASE WHEN Status = 'Cancelled' THEN TotalAmount ELSE 0 END) AS LostRevenue
FROM Orders
GROUP BY VendorId;

5. Senior Window Functions (`ROW_NUMBER`, `RANK`, `DENSE_RANK`, `LEAD`, `LAG`)

Senior 7-8 YOE Topic

5.1 ROW_NUMBER() vs RANK() vs DENSE_RANK() Matrix

Function	Behavior on Tied Values (e.g. Salaries $100k, $100k, $90k)	Rank Output Sequence
`ROW_NUMBER()`	Assigns a unique, sequential integer to every row regardless of ties.	`1, 2, 3`
`RANK()`	Assigns same rank to ties, but SKIPS subsequent ranks.	`1, 1, 3` (Rank 2 is skipped!)
`DENSE_RANK()`	Assigns same rank to ties, WITHOUT SKIPPING subsequent ranks.	`1, 1, 2` (No gaps!)

5.2 Real-World Window Function Patterns

Pattern 1: Month-Over-Month Sales Growth using `LAG()`

WITH MonthlySales AS (
  SELECT 
    FORMAT(OrderDate, 'yyyy-MM') AS SalesMonth,
    SUM(TotalAmount) AS CurrentMonthSales
  FROM Orders
  GROUP BY FORMAT(OrderDate, 'yyyy-MM')
)
SELECT 
  SalesMonth,
  CurrentMonthSales,
  LAG(CurrentMonthSales, 1) OVER (ORDER BY SalesMonth) AS PreviousMonthSales,
  (CurrentMonthSales - LAG(CurrentMonthSales, 1) OVER (ORDER BY SalesMonth)) AS GrowthAmount
FROM MonthlySales;

Pattern 2: Safely Deleting Duplicate Rows with `ROW_NUMBER()`

-- Deduplicates Users table by Email keeping ONLY the earliest registered row:
WITH DuplicateCTE AS (
  SELECT Id, Email, CreatedDate,
    ROW_NUMBER() OVER (
      PARTITION BY Email 
      ORDER BY CreatedDate ASC
    ) AS RowNum
  FROM Users
)
DELETE FROM DuplicateCTE WHERE RowNum > 1;

6. Transactions, ACID, Isolation Levels & Deadlocks

Transactions & Locking

6.1 Real-World E-Commerce Checkout Transaction Flow

BEGIN TRANSACTION;
BEGIN TRY
  -- 1. Create Order Master Header
  INSERT INTO Orders (CustomerId, TotalAmount, Status) VALUES (@CustId, @Total, 'Pending');
  DECLARE @OrderId INT = SCOPE_IDENTITY();

  -- 2. Deduct Inventory Stock (Pessimistic Row Lock)
  UPDATE Inventory WITH (UPDLOCK, ROWLOCK)
  SET QuantityOnHand = QuantityOnHand - @Qty
  WHERE ProductId = @ProdId AND QuantityOnHand >= @Qty;

  IF @@ROWCOUNT = 0
    THROW 50001, 'Insufficient stock available!', 1;

  -- 3. Commit atomic transaction
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
  THROW;
END CATCH;

6.2 Deadlock Prevention Checklist in .NET / C#

💡 Deadlock Prevention Strategy for Team Leads:
1. Consistent Object Access Order: Ensure ALL stored procedures/services touch tables in identical order (e.g. `Orders` THEN `Inventory` THEN `Payments`).
2. Polly Retry Policy in .NET: Wrap EF Core database calls with a retry policy catching `SqlException` Error 1205 (Deadlock victim) to automatically retry up to 3 times.
3. Keep Transactions Short: Never perform external HTTP API calls or slow file I/O inside an active SQL transaction!

7. Index Performance, Execution Plans & Slow Query Troubleshooting

Performance Optimization

7.1 The 11-Step Production SQL Troubleshooting Checklist

1. Reproduce slow API locally using production-like dataset.
2. Capture SQL query generated by EF Core via SQL Server Profiler / Extended Events.
3. Generate Actual Execution Plan in SSMS (Ctrl + M).
4. Check for Table Scans or Key Lookups (RID Lookup / Clustered Index Lookup).
5. Check for non-SARGable WHERE predicates (e.g. `WHERE YEAR(CreatedDate) = 2026`).
6. Verify if indexes are being used (Index Seek vs Index Scan).
7. Check statistics freshness (`sp_updatestats`).
8. Add Covering Index with `INCLUDE` to eliminate Key Lookups.
9. Rewrite query to use Keyset Seek Pagination instead of `OFFSET/FETCH`.
10. Test optimized query against 10,000,000 rows.
11. Monitor CPU, IO, and Execution Plan in Production Query Store post-deployment.

7.2 SARGable Queries vs Non-SARGable Queries (Top Performance Bug)

🚨 Non-SARGable Bug:
Wrapping an indexed column in a function like WHERE UPPER(CustomerName) = 'JOHN' or WHERE YEAR(OrderDate) = 2026 DISABLES INDEX SEEKS and forces SQL Server to scan all 10 Million rows!

-- ❌ BAD (Non-SARGable - Triggers 10 Million Row Table Scan):
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;

-- ✅ GOOD (SARGable - Triggers 1ms B-Tree Index Seek!):
SELECT * FROM Orders 
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

8. SQL + .NET / EF Core Integration & Performance

EF Core & LINQ Masterclass

8.1 IQueryable vs IEnumerable (Top .NET Interview Question)

Feature	`IQueryable`	`IEnumerable`
Execution Location	Executes on Database Server! Translates LINQ into native SQL query.	Executes in Application RAM! Pulls all records over network first.
Filter Evaluation	Appends `.Where()` to the generated SQL query (`WHERE Price > 100`).	Fetches 1,000,000 rows into server memory, then filters in C#! 💥
Best Practice	Always build queries as `IQueryable` before calling `.ToListAsync()`.	Use only after data has already been fetched into in-memory lists.

8.2 Fixing N+1 Queries & Optimization in EF Core

// ❌ BAD: Loads 100,000 orders + tracks them in memory + triggers N+1 queries!
var badOrders = await _context.Orders.ToListAsync();

// ✅ SENIOR BEST PRACTICE: Eager Loading + Projection + AsNoTracking
var optimizedOrders = await _context.Orders
  .AsNoTracking() // 👈 Disables change tracker (saves 50% memory)
  .Where(o => o.Status == "Completed")
  .Select(o => new OrderDto { // 👈 Projection: Queries ONLY 3 required columns!
    OrderId = o.Id,
    CustomerName = o.Customer.CustomerName,
    TotalAmount = o.TotalAmount
  })
  .ToListAsync();

9. Real-World Architecture Scenarios (Angular ➡️ .NET ➡️ SQL)

Enterprise System Architecture

9.1 Inventory Allocation Architecture (Preventing Over-Selling under High Traffic)

🛍️ Flash Sale Ticket Booking Scenario:
1,000 concurrent users click "Buy" on the last 5 remaining iPhone stock items at the exact same millisecond!

Architecture Solution: Combine Optimistic Concurrency Control (`RowVersion`) with SQL Row-Level Locks (`UPDLOCK`) inside a scoped database transaction to guarantee stock quantity never drops below 0!

public async Task<bool> AllocateStockAsync(int productId, int qty) {
  using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted);
  try {
    // Raw SQL Row Lock guarantees exclusive update access:
    var rowsAffected = await _context.Database.ExecuteSqlInterpolatedAsync(
      $"UPDATE Inventory WITH (UPDLOCK, ROWLOCK) SET Quantity = Quantity - {qty} WHERE ProductId = {productId} AND Quantity >= {qty}"
    );

    if (rowsAffected == 0) {
      await transaction.RollbackAsync();
      return false; // Stock unavailable
    }

    await _context.SaveChangesAsync();
    await transaction.CommitAsync();
    return true;
  } catch {
    await transaction.RollbackAsync();
    throw;
  }
}

10. Senior Coding Round Problems & Top Interview Q&A (7-8 YOE)

Coding Round & Interview Q&A

10.1 Top Coding Round Problems

Problem 1: Nth Highest Salary per Department

WITH DepartmentSalaries AS (
  SELECT 
    EmployeeName, 
    DepartmentId, 
    Salary,
    DENSE_RANK() OVER (
      PARTITION BY DepartmentId 
      ORDER BY Salary DESC
    ) AS SalaryRank
  FROM Employees
)
SELECT EmployeeName, DepartmentId, Salary 
FROM DepartmentSalaries 
WHERE SalaryRank = @N; -- 👈 Change @N for 2nd, 3rd, or Nth highest salary

Problem 2: Gaps & Islands (Finding Consecutive Active Days)

WITH GroupedDates AS (
  SELECT 
    UserId, 
    LogDate,
    DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LogDate), LogDate) AS GroupKey
  FROM UserActivityLogs
)
SELECT UserId, MIN(LogDate) AS StreakStart, MAX(LogDate) AS StreakEnd, COUNT(*) AS ConsecutiveDays
FROM GroupedDates
GROUP BY UserId, GroupKey
HAVING COUNT(*) >= 3;

10.2 Top Senior Interview Scenario Q&A

❓ Interviewer Question: "Your production SQL Server CPU spikes to 95% suddenly. What steps do you take to troubleshoot and resolve this live incident?"

💡 Best Senior Answer:
1. Run sys.dm_exec_requests joined with sys.dm_exec_sql_text to find active queries with highest `cpu_time` and status `running`.
2. Check for blocking using sp_who2 or sys.dm_os_waiting_tasks.
3. Inspect Query Store to see if a execution plan regression occurred (parameter sniffing).
4. If a regression occurred, force the last known good plan using sp_query_store_force_plan.
5. If caused by missing index or stale stats, update statistics (`UPDATE STATISTICS`) or deploy covering index under change management.

11. Temp Tables vs Table Variables vs CTEs & Advanced SQL Features

Advanced SQL Server Features

11.1 Temp Tables (`#Temp`) vs Table Variables (`@Table`) vs CTEs

Feature	Temp Table (`#Temp`)	Table Variable (`@Table`)	CTE (`WITH ... AS`)
Storage Location	Physical disk in `tempdb`	RAM memory + `tempdb` overflow	Zero physical storage (Inline SQL View)
Statistics & Indexes	YES ✅ Full statistics & custom indexes	NO STATISTICS ❌ (Compiler assumes 1 row!)	N/A (Uses underlying table stats)
Scope	Session / Stored Procedure scope	Batch / Block scope only	Single query statement scope
Best Dataset Size	Large Datasets (> 10,000 rows)	Tiny Datasets (< 100 rows)	Recursive queries / Code readability

11.2 Native JSON Support in SQL Server (`OPENJSON`, `JSON_VALUE`)

-- 1. Parse array JSON input sent from .NET API into a virtual SQL table:
DECLARE @JsonInput NVARCHAR(MAX) = '[{"id":101,"qty":5},{"id":102,"qty":2}]';

SELECT * 
FROM OPENJSON(@JsonInput)
WITH (
  ProductId INT '$.id',
  Quantity INT '$.qty'
);

11.3 System-Versioned Temporal Tables (Automated Audit History)

-- Automatically records every historical UPDATE/DELETE into an audit history table!
CREATE TABLE Offers (
  Id INT PRIMARY KEY IDENTITY(1,1),
  Title NVARCHAR(200) NOT NULL,
  Price DECIMAL(18,2) NOT NULL,
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
  SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OffersHistory));

-- Query historical state of table as it existed on a specific date!
SELECT * FROM Offers FOR SYSTEM_TIME AS OF '2026-01-01 12:00:00';

12. 7-Day & 1-Day SQL Interview Revision Cheat Sheet

Revision Roadmap

📅 7-Day Interview Revision Plan:

Day 1: Master Basics, Constraints, Data Types (`DECIMAL` vs `FLOAT`, `VARCHAR` vs `NVARCHAR`), and CRUD NULL handling (`COALESCE`, `NULLIF`).
Day 2: Deep dive into All JOINs, `NOT EXISTS` vs `LEFT JOIN` vs `NOT IN` NULL trap, and deduplicating rows.
Day 3: Practice `GROUP BY`, `HAVING` vs `WHERE`, and Window Functions (`ROW_NUMBER`, `DENSE_RANK`, `LEAD`, `LAG`).
Day 4: Master Transactions (`BEGIN TRAN ... COMMIT`), ACID, Isolation Levels, and Deadlock (Error 1205) troubleshooting.
Day 5: Learn B-Tree Indexing (Clustered vs Non-Clustered), Covering Indexes (`INCLUDE`), Filtered Indexes, and SARGable queries.
Day 6: Review EF Core Integration (`IQueryable` vs `IEnumerable`), `AsNoTracking()`, Projection, and N+1 query fixes.
Day 7: Practice SQL Coding Round problems (Nth highest salary, Gaps & Islands) and 95% CPU production incident troubleshooting.

⚡ 1-Day Quick Memory Booster:

• Query Order: `FROM` ➡️ `JOIN` ➡️ `WHERE` ➡️ `GROUP BY` ➡️ `HAVING` ➡️ `SELECT` ➡️ `DISTINCT` ➡️ `ORDER BY` ➡️ `OFFSET`.
• Currency: Always `DECIMAL(18,2)`, NEVER `FLOAT`!
• Null Check: Prefer `NOT EXISTS` over `NOT IN` (which fails on NULLs!).
• Ranking: `DENSE_RANK()` does NOT skip rank numbers after ties (`1, 1, 2`).
• Indexing: Use `INCLUDE` to create Covering Indexes and eliminate Key Lookups.
• EF Core: `IQueryable` runs on DB server; `IEnumerable` runs in C# RAM. Always use `AsNoTracking()` for read queries.

Codebes