100 MS SQL Server Interview Questions for Senior Developers 🚀

-

 


Introduction

Microsoft SQL Server is one of the most widely used relational database management systems in enterprise applications. For senior developers, especially those working with .NET and backend systems, mastering SQL Server concepts is essential for building efficient, secure, and scalable applications. This blog organizes 100 commonly asked SQL Server interview questions into structured sections, making it easier to study and prepare.

1. SQL Basics

  1. What is SQL?

  2. Difference between SQL and T-SQL?

  3. Types of SQL commands?

  4. Difference between DELETE, TRUNCATE, and DROP?

  5. What is NULL?

  6. Difference between NULL and empty string?

  7. What is identity column?

  8. What is default constraint?

  9. What is check constraint?

  10. What is unique constraint?

2. Joins

  1. What is JOIN?

  2. Types of joins?

  3. Difference between INNER JOIN and LEFT JOIN?

  4. Difference between LEFT JOIN and RIGHT JOIN?

  5. What is FULL OUTER JOIN?

  6. What is SELF JOIN?

  7. What is CROSS JOIN?

  8. What is CROSS APPLY?

  9. What is OUTER APPLY?

  10. When to use APPLY?

3. Keys / Constraints

  1. What is Primary Key?

  2. What is Foreign Key?

  3. What is Composite Key?

  4. Candidate Key?

  5. Alternate Key?

  6. Super Key?

  7. What is Index?

  8. Clustered Index vs Non-Clustered Index?

  9. Unique Index?

  10. Filtered Index?

4. Stored Procedure / Functions

  1. What is Stored Procedure?

  2. Advantages of Stored Procedure?

  3. What is Function?

  4. Difference between Procedure and Function?

  5. Scalar Function?

  6. Table-Valued Function?

  7. Inline TVF vs Multi-statement TVF?

  8. Can SP return value?

  9. Output parameter in SP?

  10. Dynamic SQL?

5. Views

  1. What is View?

  2. Advantages of View?

  3. Indexed View?

  4. Materialized view?

  5. Can we update a view?

  6. Difference between View and Table?

  7. What is schema binding?

6. Normalization

  1. What is normalization?

  2. 1NF?

  3. 2NF?

  4. 3NF?

  5. BCNF?

  6. Denormalization?

  7. When to denormalize?

7. Transactions

  1. What is transaction?

  2. ACID properties?

  3. BEGIN / COMMIT / ROLLBACK?

  4. Savepoint?

  5. Nested transaction?

  6. Distributed transaction?

8. Locks / Deadlocks

  1. What is lock?

  2. Types of locks?

  3. Shared lock?

  4. Exclusive lock?

  5. Update lock?

  6. What is blocking?

  7. What is deadlock?

  8. How to identify deadlock?

  9. How to prevent deadlock?

  10. What is NOLOCK?

9. Index / Performance

  1. How index works?

  2. Why query slow?

  3. Execution plan?

  4. Clustered index scan vs seek?

  5. Non-clustered index seek?

  6. Covering index?

  7. Included column?

  8. Fragmentation?

  9. Rebuild vs Reorganize?

  10. Statistics in SQL Server?

10. Advanced SQL

  1. CTE?

  2. Recursive CTE?

  3. Temp table vs Table variable?

  4. Difference between HAVING and WHERE?

  5. ROW_NUMBER(), RANK(), DENSE_RANK()?

  6. Partitioning?

  7. Pivot and Unpivot?

  8. MERGE statement?

  9. XML/JSON in SQL?

  10. Dynamic pivot?

11. Backup / Security / Real-world

  1. Full backup vs Differential backup vs Log backup?

  2. Recovery model?

  3. Mirroring / Replication / Log shipping?

  4. Always On Availability Group?

  5. SQL Profiler?

  6. How to optimize stored procedure?

  7. Parameter sniffing?

  8. How to troubleshoot production DB issue?

  9. How to secure SQL Server?

  10. What are common mistakes in DB design?

Conclusion

These 100 questions cover the breadth of SQL Server knowledge expected from senior developers. From basics and joins to advanced performance tuning, transactions, and real-world scenarios, this list serves as a comprehensive guide for interview preparation. By mastering these topics, you’ll not only be ready for interviews but also equipped to design and manage efficient, secure, and scalable database systems.



This was part of Interview Preparation With Bipin — Let’s Crack It!

Comments

Post a Comment

Popular posts from this blog

Angular Architecture

-
Image
Angular Architecture is divided into different parts like Component, Template, Directive, Service, and Module. Before you start to develop your application, you should have knowledge about the purpose of each part of the application. So that you can make your application smoother and faster. Below is the figure of angular architecture. Angular Architecture In this chapter, we will see each term in detail. Module Angular Applications are modular and Angular has its own modularity system called NgModule. Every Angular application has at least one class with a @NgModule decorator, it is the root module, conventionally named as AppModule. To use any component into an application you need to declare it into the related module. Angular Module is class with a @NgModule decorator. Code Snippet of Angular Module The most important properties of @NgModule are : declarations:  declaration property contains ...
Read more

Why should I learn Angular?

-
It is one of the most growing and advanced technology for create a Single Page Applications(SPA). Here the things why you should learn Angular. It is one of the Google Technology Easy to Learn, if you’re familiar with Java Script and UI Nowadays Single Page Applications(SPA) is the trend. If you want to increase your career opportunities Having a lot of Fun while working with Angular If you’re familiar with OOPS then Angular 4 is easy to code Typescript which helps you to create OOPS based Front End Design Lot of resources available to learn Angular And lot more…
Read more

Solid Principle

-
SOLID Principle What are the SOLID principles? Five object-oriented design principles  help make code: Maintainable (easy to change without breaking things) Extensible (easy to add new features) Testable (easy to write unit tests) Understandable (easy for other developers to read) The five principles: S – Single Responsibility Principle (SRP) One class = one reason to change. O – Open/Closed Principle (OCP) Open for extension, closed for modification. L – Liskov Substitution Principle (LSP) Subclasses should work wherever base classes work (no broken contracts). I – Interface Segregation Principle (ISP) Clients should not be forced to depend on methods they don’t use. D – Dependency Inversion Principle (DIP) Depend on abstractions, not concrete classes. Why are we using SOLID principles? Reduce bugs when requirements change → You can modify code without breaking unrelated parts. Improve maintainability → Code is easier to und...
Read more