Wednesday, November 26, 2025

, ,

MS SQL Server Interview Questions - Part 1

 Microsoft SQL Server continues to be one of the most widely used relational database platforms in the industry. Whether you are preparing for roles such as SQL Developer, MSBI Developer, Data Engineer, Database Administrator (DBA), or Support Engineer, this comprehensive set of interview questions will help you cover all fundamental to advanced concepts.

This guide is structured topic-wise for easier learning and quick revision.

1. Basic SQL Concepts

1. What is SQL Server?

A relational database management system (RDBMS) developed by Microsoft that supports transaction processing, analytics, and business intelligence workloads.

2. What are tables, rows, and columns?

  • Table: Structured set of data.

  • Rows: Records in a table.

  • Columns: Attributes of the data.

3. What are Primary Keys?

A unique identifier for each row in a table. Cannot contain NULL values.

4. What is a Foreign Key?

A field that establishes a relationship between two tables.

5. What is a Unique Key?

Ensures all values in the column are distinct; allows a single NULL.

2. SQL Queries (DML / DQL)

6. Difference between WHERE and HAVING?

  • WHERE: Filters rows before grouping.

  • HAVING: Filters groups after aggregation.

7. What is a JOIN? Name different types.

Combines rows from multiple tables.
Types: INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF JOIN.

8. What is a Subquery?

A query within another query.

9. Write a query to find the second highest salary.

SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

10. How do you find duplicate records?

SELECT Name, COUNT(*) 
FROM Employees 
GROUP BY Name 
HAVING COUNT(*) > 1;

3. DDL Commands

11. What is the difference between TRUNCATE and DELETE?

  • DELETE: Logs each row, supports WHERE, slower.

  • TRUNCATE: Removes all rows, minimal logging, resets identity.

12. What is the ALTER command used for?

Used to modify an existing table schema (add/modify/drop columns).

4. Constraints

13. Types of Constraints?

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

14. What is a CHECK constraint?

Ensures values meet a condition.

5. Normalization & Schema Design

15. What is Normalization?

A process to organize data to reduce redundancy and improve data integrity.

16. Name different Normal Forms.

1NF, 2NF, 3NF, BCNF, 4NF, 5NF.

17. What is Denormalization?

Introducing redundancy to improve read performance.

6. Transactions & ACID Properties

18. Define ACID properties.

Atomicity, Consistency, Isolation, Durability.

19. What is a Transaction?

A logical unit of work executed as a single operation.

20. What are Isolation Levels?

Read Uncommitted, Read Committed, Repeatable Read, Serializable, Snapshot.

7. Functions (Scalar & Aggregate)

21. Difference between Scalar & Aggregate functions?

  • Scalar: Returns a single value.

  • Aggregate: Works on a set of rows.

22. Example of Scalar Function?

LEN(), UPPER(), GETDATE()

23. Example of Aggregate Function?

SUM(), AVG(), MAX(), COUNT()

8. Stored Procedures, Functions, Views, Triggers

24. What is a Stored Procedure?

Precompiled set of SQL statements that can accept parameters.

25. What is the difference between Function and Stored Procedure?

FunctionStored Procedure
Must return valueOptional return
Cannot modify DB stateCan modify DB
Can be used in SELECTCannot be used in SELECT

26. What is a View?

A virtual table built from a query.

27. What is a Trigger?

Automatic execution of SQL code when an event occurs (Insert/Update/Delete).

9. Indexing (Clustered & Non-Clustered)

28. What is an Index?

Improves query performance by optimizing search operations.

29. Difference between Clustered and Non-Clustered Index?

  • Clustered: Defines physical order of data. One per table.

  • Non-Clustered: Logical order stored separately. Many per table.

30. What is a Covering Index?

An index containing all columns required by a query.

10. Performance Tuning

31. How do you analyze slow queries?

Using execution plans, SET STATISTICS IO/TIME, indexing analysis.

32. What are Execution Plans?

A graphical/text representation showing how SQL Server executes a query.

33. What causes Table Scans?

Missing indexes, poor filter conditions.

34. How do you reduce fragmentation?

Rebuild or reorganize indexes.

11. Joins – Advanced Questions

35. What is the difference between INNER and OUTER JOIN?

  • INNER: Returns matching rows only.

  • OUTER: Returns matching + non-matching rows.

36. What is CROSS APPLY vs OUTER APPLY?

Used with table-valued functions to join with correlated queries.

12. SQL Server Architecture

37. What is SQL Server Agent?

Used for scheduling jobs (backups, scripts, maintenance).

38. What is TempDB?

A system database used for temporary objects and sorts.

39. What is a Checkpoint?

Process of writing dirty pages from buffer cache to disk.

13. Backups & Recovery

40. Types of Backups?

Full, Differential, Transaction Log, File/Filegroup.

41. What is Point-in-Time Recovery?

Restoring the database to a specific moment using T-Logs.

14. Security

42. What is Authentication vs Authorization?

  • Authentication: Identity verification.

  • Authorization: Access rights.

43. What are SQL Server Roles?

Server-level and database-level predefined roles for access control.

15. Advanced Topics

44. What is Partitioning?

Splitting a large table into smaller pieces.

45. What is Replication?

Copying and distributing data across servers.

46. What is AlwaysOn Availability Group?

High availability and disaster recovery feature.

47. What is SQL Profiler?

Tool for tracing SQL Server events.

48. What is Deadlock?

Two processes waiting on each other’s resources.

49. How do you detect Deadlocks?

Using SQL Profiler, Extended Events, or by reviewing error logs.

50. How do you resolve Deadlocks?

Optimize queries, add indexes, ensure consistent locking order.

0 comments:

Post a Comment