IndianTechnoEra: SQL Server Mastery for .NET Developer

🔥 SQL Server Mastery for .NET Backend Developers (Hardened Edition)

Target: .NET developer with ~2 years experience → Performance engineer & database architect.
Critical additions: SQL Server Internals, Query Anti-Patterns, Pagination at Scale, High Availability, Bulk ETL patterns.

---

📘 MODULE 1: FOUNDATIONS & RELATIONAL DESIGN (Beginner → Intermediate)

Chapter 1: Beyond CRUD – Thinking in Sets

• 1.1 Relational model vs object-oriented mindset (from LINQ to set-based)
• 1.2 Declarative set-based logic – why loops/cursors are your last resort
• 1.3 Data types deep dive: choosing right types for performance & precision (avoiding implicit conversions)
• 1.4 NULL semantics in SQL Server (3-valued logic) – common .NET pitfalls
• 1.5 Identity, Sequences, and GUIDs – when to use each with .NET's Guid vs int, clustered key impact

Chapter 2: Professional Database Design

• 2.1 Normalization (1NF to 3NF) – why denormalization is a conscious trade-off
• 2.2 Primary keys, foreign keys, unique constraints in real systems
• 2.3 Relationships: 1:1, 1:N, N:N – mapping to Entity Framework / Dapper
• 2.4 Check constraints, default constraints, and business rule enforcement
• 2.5 Multi-tenant database patterns (separate DB, separate schema, tenant column + RLS)
• 2.6 Designing for soft-delete, audit trails, and temporal tables (system-versioned)

Project 1: E-Commerce product catalog + multi-tenant orders DB

• Design normalized schema, handle tenant isolation, write migration scripts with proper constraints.

⚡ MODULE 2: QUERY WRITING & ANTI-PATTERNS (Intermediate → Hardcore)

Chapter 3: Advanced T-SQL for Backend Developers

• 3.1 Window functions (ROW_NUMBER, RANK, LEAD/LAG) – replacing self-joins and subqueries
• 3.2 Common Table Expressions (CTE) – recursive queries for hierarchies
• 3.3 PIVOT, UNPIVOT, and grouping sets for reporting
• 3.4 APPLY operator (CROSS APPLY vs OUTER APPLY) – when .NET transitions to SQL
• 3.5 Dynamic SQL & parameterized queries – SQL injection prevention in .NET

Chapter 4: 🚨 Query Anti-Patterns & Performance Killers (NEW - MANDATORY)

• 4.1 SELECT * and over-fetching – network I/O, covering index bypass
• 4.2 Implicit conversions (INT vs VARCHAR, datetime vs datetime2) – index scan guaranteed
• 4.3 Functions on indexed columns (WHERE YEAR(date) = 2025) – how to rewrite
• 4.4 OR conditions killing indexes – when to use UNION/IN/NOT EXISTS instead
• 4.5 RBAR (Row By Agonizing Row): cursors, while loops, recursive CTE misuse
• 4.6 Scalar UDF performance disaster (multi-statement TVFs also) – inline TVF salvation
• 4.7 N+1 query problem (from .NET ORM) – detection and batching

Project 2: Refactor a monster stored procedure

• Given a slow proc with all anti-patterns, rewrite set-based, add indexes, compare execution plans.

🧠 MODULE 3: SQL SERVER INTERNALS & DATA FLOW (Critical Foundation)

Chapter 5: How SQL Server Works Under the Hood

• 5.1 Pages (8 KB), extents (64 KB), IAM – physical storage layout
• 5.2 Buffer pool and memory management – how data is cached, read-ahead
• 5.3 Transaction log internals and Write-Ahead Logging (WAL) – why WRITELOG wait exists
• 5.4 Checkpoints (automatic/manual), recovery models (FULL, BULK_LOGGED, SIMPLE)
• 5.5 How a query executes step-by-step: parsing → binding → optimization → execution (iterator model)
• 5.6 Lazywriter, checkpoint, log flush – impact on application performance

Chapter 6: Execution Plans Deep Dive

• 6.1 Scan vs Seek – estimating I/O (logical reads vs physical reads)
• 6.2 Join operators: Nested Loops, Hash Match, Merge Join – cost estimation & when each shines
• 6.3 Spools, Sorts, Window aggregates – expensive operators
• 6.4 Key Lookup (clustered) / RID Lookup (heap) – why they kill performance
• 6.5 STATISTICS IO, TIME, and live query stats

Project 3: Capture and interpret actual execution plan

• Run a production-like workload, capture SET STATISTICS IO ON, identify high logical reads, change indexing.

🗂️ MODULE 4: INDEXING ARCHITECTURE & STRATEGIES (Intermediate → Advanced)

Chapter 7: Index Internals and Strategy

• 7.1 Clustered vs Nonclustered – B+Tree structure, page splits, fillfactor
• 7.2 Covering indexes, included columns, filtered indexes
• 7.3 Composite index key order – equality vs range predicates (left-most prefix rule)
• 7.4 Index maintenance: fragmentation, reorganize vs rebuild, online vs offline
• 7.5 Columnstore indexes for analytics – batch mode execution

Chapter 8: Advanced Indexing for Real-World Workloads

• 8.1 Indexing for ORDER BY, GROUP BY, DISTINCT – avoiding sorts
• 8.2 Index intersection vs composite indexes – the optimizer's choice
• 8.3 SARGability – writing WHERE clauses that use indexes (no functions, no implicit conversions)
• 8.4 Indexing on foreign keys, bit columns, and wide strings (prefix indexes?)
• 8.5 Monitoring missing indexes (DMVs) and index usage statistics

Project 4: Index tuning marathon

• Take OLTP schema, run 20 concurrent queries, use sp_BlitzIndex style analysis, prove improvements.

🎛️ MODULE 5: STORED PROCEDURES & ENTERPRISE T-SQL (Advanced)

Chapter 9: Production-Ready Stored Procedures

• 9.1 Parameterized procs, output parameters, return codes – error handling in .NET
• 9.2 SET NOCOUNT ON, XACT_ABORT, TRY/CATCH patterns
• 9.3 Transaction management inside stored procedures (savepoints, nested transactions)
• 9.4 Idempotency patterns – handling retries from .NET Polly (INSERT IF NOT EXISTS, MERGE with caution)
• 9.5 Versioning stored procedures (schema binding, deployment strategies)
• 9.6 Logging inside procedures (sp_who, custom log tables) and debugging long-running procs

Chapter 10: Advanced Programming Constructs

• 10.1 Table-valued parameters (TVP) – bulk data from .NET DataTable / IEnumerable (Dapper/ADO.NET)
• 10.2 Temporary tables (#temp) vs Table variables (@table) – tempdb contention, cardinality estimates
• 10.3 MERGE statement – upsert pattern with caution (concurrency issues, triggers)
• 10.4 OUTPUT clause – returning inserted/updated rows to .NET
• 10.5 Error handling with THROW, RAISERROR, and custom error messages

Project 5: Bulk order import procedure with error logging + idempotency

• Build a stored proc that accepts TVP, handles duplicates safely for retries, logs errors, returns summary.

🔐 MODULE 6: TRANSACTIONS & CONCURRENCY CONTROL (Expert)

Chapter 11: ACID, Isolation Levels, and Locking Internals

• 11.1 Read Uncommitted, Read Committed (default), Snapshot, Repeatable Read, Serializable – practical scenarios
• 11.2 Lock types: shared, exclusive, update, intent locks – blocking basics
• 11.3 Read Committed Snapshot Isolation (RCSI) – reducing blocking in .NET apps (the #1 recommended setting)
• 11.4 Deadlocks: detection, graph analysis, and resolution patterns (retry logic in .NET with Polly)
• 11.5 Optimistic vs Pessimistic concurrency – mapping to EF Core concurrency tokens (rowversion)

Chapter 12: Application Transaction Patterns

• 12.1 TransactionScope in .NET (lightweight vs distributed) – DTC avoidance
• 12.2 Explicit transactions in stored procedures vs client-side transactions
• 12.3 Minimizing lock escalation and long-running transactions
• 12.4 Using sp_getapplock for custom application locks
• 12.5 Queues and transactional outbox pattern with SQL Server

Project 6: Design a high-contention booking system

• Implement seat reservation, handle deadlocks with retry logic in C#, use RCSI, prove with load test.

📄 MODULE 7: PAGINATION, FILTERING & SEARCH AT SCALE (Critical for .NET APIs)

Chapter 13: Pagination Strategies for Large Data

• 13.1 OFFSET/FETCH pitfalls (scanning all previous rows, O(N^2) cost)
• 13.2 Keyset pagination (seek method) – WHERE clause on last seen values, ideal for infinite scroll
• 13.3 Dynamic sorting with large datasets – indexed ORDER BY tricks
• 13.4 Efficient filtering with dynamic conditions – avoiding parameter sniffing, using OPTION (RECOMPILE) or dynamic SQL
• 13.5 Full-text search basics (CONTAINS, FREETEXT) vs LIKE '%term%' – performance implications
• 13.6 Combining pagination, filtering, and sorting in a single stored procedure for .NET backend

Project 7: Build a product search API

• Implement keyset pagination + full-text search + dynamic filters, compare OFFSET/FETCH vs keyset with 1M rows.

🚚 MODULE 8: BULK DATA & ETL PATTERNS (Real Production Work)

Chapter 14: Bulk Insert Strategies

• 14.1 SqlBulkCopy in .NET – best practices, batch size, table locks
• 14.2 BULK INSERT and OPENROWSET(BULK) from files
• 14.3 Staging tables pattern – load, validate, merge into main table
• 14.4 Batching large operations (UPDATE/DELETE in chunks) to avoid transaction log explosion
• 14.5 Handling millions of rows safely – parallelism, logging, error handling

Chapter 15: Change Tracking and CDC for ETL

• 15.1 Change Tracking (lightweight, async) vs Change Data Capture (heavy, sync)
• 15.2 Incremental loads using rowversion / Change Tracking
• 15.3 Using OUTPUT deleted/inserted for audit and data warehouse sync

Project 8: Daily ETL from flat files to normalized tables

• Use staging table, SqlBulkCopy from C#, merge with change tracking, log rows processed.

🌐 MODULE 9: HIGH AVAILABILITY & SCALING BASICS (System Design Level)

Chapter 16: Scaling SQL Server for .NET Applications

• 16.1 Read replicas (Always On Availability Groups) – connection string routing, read-only intent
• 16.2 Failover basics – multi-subnet, application retry logic
• 16.3 Horizontal vs vertical scaling – where SQL Server struggles (sharding intro)
• 16.4 Sharding pattern (database-per-tenant, or manual shard map) – pros/cons
• 16.5 Azure SQL Database elastic pools, hyperscale – when to scale out

Chapter 17: Monitoring & Proactive Performance

• 17.1 Dynamic Management Views (DMVs) for index, query, and wait stats
• 17.2 Query Store – forcing plans, detecting regression
• 17.3 Waits statistics analysis (PAGEIOLATCH, LCK, WRITELOG, BACKUPTHREAD)
• 17.4 Extended Events – lightweight tracing for production (deadlocks, long queries)
• 17.5 sp_whoisactive (Adam Machanic) – standard tool for real-time troubleshooting

Project 9: Production performance audit + HA simulation

• Simulate workload, capture top expensive queries, recommend indexes, configure read replica for reporting.

🛡️ MODULE 10: SECURITY, ENCRYPTION & COMPLIANCE (Expert)

Chapter 18: SQL Server Security Deep Dive

• 18.1 Authentication: Windows vs SQL Server auth – best practices for .NET connection strings
• 18.2 Principals, Securables, Permissions – server roles, database roles, schema ownership
• 18.3 Principle of least privilege: application user should not be 'dbo' or 'sysadmin'
• 18.4 Row-Level Security (RLS) – multi-tenant isolation without per-tenant code
• 18.5 Dynamic Data Masking and Always Encrypted – protecting PII with .NET client driver

Chapter 19: Auditing & Compliance

• 19.1 SQL Server Audit (server and database level) – who accessed what
• 19.2 Temporal Tables (system-versioned) – automatic history for compliance, FOR SYSTEM_TIME queries
• 19.3 Transparent Data Encryption (TDE) – at-rest encryption without app changes
• 19.4 GDPR/HIPAA considerations – secure logging, data retention policies

Project 10: Secure multi-tenant API backend with auditing

• Implement RLS, Always Encrypted for credit cards, temporal tables for audit trails, .NET integration.

🔗 MODULE 11: .NET INTEGRATION & PRODUCTION PATTERNS (Practical Mastery)

Chapter 20: ADO.NET, Dapper, EF Core – Choosing the Right Tool

• 20.1 Connection pooling in SQL Server – how .NET's SqlConnection works under the hood
• 20.2 Raw ADO.NET for ultra-high performance (DataReader, batches, async)
• 20.3 Dapper – micro-ORM for mapping complex queries, avoiding N+1
• 20.4 EF Core – when to use, how to see generated SQL, relation mappings, compiled queries
• 20.5 Massive data operations: SqlBulkCopy vs TVP vs bcp

Chapter 21: Production Integration Patterns

• 21.1 Repository pattern and Unit of Work over SQL Server + Dapper/EF Core
• 21.2 Health checks for SQL Server (Always On, connectivity) – IHostedService / HealthCheck
• 21.3 Retry policies with Polly for transient faults (deadlock, timeout, network)
• 21.4 Async patterns in .NET – avoiding sync-over-async with AsyncDbContext
• 21.5 Database migrations (FluentMigrator, EF Migrations) in CI/CD pipeline, zero-downtime

Project 11: Complete .NET + SQL Server backend (CQRS style)

• Build minimal API with Dapper, implement CQRS using stored procedures for writes, indexed views/queries for reads, handle concurrency.

🎯 MODULE 12: INTERVIEW PREP & REAL-WORLD SCENARIOS (Capstone)

Chapter 22: The .NET Backend SQL Interview

• 22.1 Classic SQL problems (top N per group, gaps and islands, rolling totals, median)
• 22.2 Discussing indexing & keyset pagination on the whiteboard
• 22.3 Explaining deadlocks, RCSI, and parameter sniffing to senior engineers
• 22.4 System design: leaderboard (ranking), messaging queue (using SQL as queue), audit log

Chapter 23: Troubleshooting Production Outages

• 23.1 Slowdown due to missing index – identify using DMVs
• 23.2 Sudden CPU spike – parameter sniffing example
• 23.3 Blocking chain – sp_whoisactive, kill or wait?
• 23.4 TempDB out of space – large temp tables gone wild
• 23.5 Replica lag on Always On – troubleshooting

Final Capstone Project: E‑commerce performance and concurrency overhaul

• Given a legacy .NET + SQL Server app with slow checkout, deadlocks, N+1 queries, and full table scans. Refactor schema, indexes, stored procedures, and transaction logic. Provide before/after metrics (duration, reads, CPU). Implement keyset pagination for product list.

📚 Appendix & Cheat Sheets (Throughout the Journey)

• Execution plan operator reference (with examples)
• DMV queries for index, missing index, wait stats (sys.dm_db_index_usage_stats, etc.)
• Isolation level quick guide for .NET developers (with connection string settings)
• Anti-patterns quick checklist (printable)
• Keyset pagination template for C# + Dapper
• SQL Server connection string recipes: Azure, Always On listener, read-only intent

New Chapter: Statistics & Cardinality Estimation

• How SQL Server estimates row counts (histograms, density)
• Auto update statistics – when it fails
• Outdated statistics and bad execution plans
• Cardinality Estimator versions (legacy vs new)
• Fixing bad estimates (UPDATE STATISTICS, OPTION RECOMPILE)

New Chapter: Parameter Sniffing Deep Dive

• What parameter sniffing actually is (plan reuse)
• Good vs bad sniffing scenarios
• Detecting parameter sniffing in Query Store
• Fix strategies:
  • OPTION (RECOMPILE)
  • OPTIMIZE FOR UNKNOWN
  • Local variables hack
  • Plan guides

New Chapter: Caching Strategies for .NET + SQL

• When NOT to hit the database
• In-memory caching (IMemoryCache, Redis basics)
• Cache invalidation strategies
• Read-heavy vs write-heavy systems
• Combining caching with keyset pagination

New Chapter: Designing Efficient API Queries

• Avoiding chatty DB calls (N round trips problem)
• Batching queries
• Projection vs full entity loading
• DTO shaping at DB level vs application level
• Handling large payloads and streaming

---

✅ Hardened TOC complete – 12 modules, 23 chapters, 11 hands-on projects, plus final capstone.
All missing pieces (Internals, Anti-Patterns, Pagination, Bulk, HA) are now core chapters.

🚀 Ready for Phase 2. Which chapter should I generate first?
Strategic recommendation: Start with Chapter 5 (SQL Server Internals) – because without pages/buffer pool/log, indexing and tuning remain guessing.
Or start with Chapter 4 (Anti-Patterns) for immediate practical value. Your call.