跳到主要内容

🟢 1. Database Design & Keys (FOUNDATION)

👉 Very frequently asked at the beginning

  • What is a primary key?
  • What is a foreign key?
  • Can a table exist without PK?
  • Composite key — pros/cons?
  • Why should PK not change?
  • Difference between PK and UK?
  • Can PK be non-clustered?

🔵 2. Constraints & Data Types

👉 Basic but important

  • Constraints in SQL Server?
  • Types in SQL Server?
  • What is IDENTITY column?

🟣 3. SQL Query Basics (WRITING vs EXECUTION)

👉 VERY COMMON interview trap

  • Written order for SQL query?
  • Execution order for SQL query?
  • What is DISTINCT?
  • Difference between WHERE and HAVING?
  • Difference between DELETE vs TRUNCATE vs DROP

🟡 4. Normalization & Data Modeling

👉 Tests database design thinking

  • 3NF

🔴 5. Indexing (VERY IMPORTANT 🚨)

👉 This is one of the MOST important sections

  • What is an index?
  • Clustered vs non-clustered index
  • Is PK always clustered?
  • What is a heap table?
  • What happens without clustered index?
  • What is composite index?
  • Why column order matters?
  • What is covering index?
  • What is index fragmentation?
  • Why GUID is bad as clustered index?
  • How to design indexes?

🟠 6. Transactions & Concurrency

👉 High-level backend understanding

  • ACID
  • What is a transaction?
  • COMMIT vs ROLLBACK
  • What is isolation level?

🟢 7. Joins & Query Logic

👉 Core SQL skills

  • INNER JOIN, LEFT JOIN, RIGHT JOIN
  • EXISTS vs IN (also repeated)
  • Difference between EXISTS and IN

🔵 8. Performance & Query Optimization (CRITICAL 🚨)

👉 This is where most candidates fail

  • What is query optimization?
  • Why is query slow?
  • What is execution plan?
  • What is SARGable query?
  • Why functions in WHERE are bad?
  • Why SELECT * is bad?
  • When index is not used?
  • Why LIKE '%abc%' is slow?
  • How to optimize a slow query?

🟣 9. Advanced / Real-world Issues (VERY IMPORTANT)

👉 This shows you are not junior anymore

  • What is N+1 problem?
  • How to fix N+1?
  • Include vs Select?
  • When to use AsEnumerable?
  • IQueryable vs IEnumerable?
  • What is deferred execution?

🟡 10. Database Objects

👉 Basic but expected

  • What is a view?
  • What is a stored procedure?
  • What is a function?