SQL Server Interview Questions

SQL Server is Microsoft's relational database management system (RDBMS) used to store, manage and retrieve data. It supports Transact-SQL (T-SQL), stored procedures, triggers, views, indexes, transactions and security features for building database applications. It enables users to:

• Store and manage relational data.
• Write T-SQL queries and database programs.
• Improve query performance using indexes.
• Maintain data integrity and security.

Beginner Interview Questions

1. What are the features of SQL Server?

Some important features of SQL Server are:

• Supports T-SQL for database programming.
• Provides high security through authentication and permissions.
• Supports transactions and backup & recovery.
• Includes stored procedures, views, triggers and functions.
• Improves performance using indexes and query optimization.

2. What are the components of SQL Server?

The main components of SQL Server include:

• Database Engine for storing and processing data.
• SQL Server Management Studio (SSMS) for database administration.
• SQL Server Agent for scheduling and automating tasks.
• Integration Services (SSIS) for data integration and ETL.
• Analysis Services (SSAS) for analytical processing.
• Reporting Services (SSRS) for generating reports.

3. What is T-SQL?

T-SQL (Transact-SQL) is Microsoft's extension of SQL. It adds programming features such as variables, loops, conditions, exception handling, stored procedures and functions.

4. What is SQL Server Management Studio (SSMS)?

SQL Server Management Studio (SSMS) is Microsoft's graphical tool used to manage SQL Server databases, execute T-SQL queries and perform administrative tasks.

5. What is the difference between SQL and T-SQL?

6. What is a primary key?

A primary key uniquely identifies each row in a table. It does not allow duplicate or NULL values and automatically creates a unique index.

7. What is an identity column?

An identity column automatically generates numeric values for new rows.

Example:

EmployeeID INT IDENTITY(1,1)

The first value starts from 1 and increments by 1 for each new row.

8. What are stored procedures?

A stored procedure is a collection of SQL statements stored in the database that can be executed repeatedly.

Benefits:

• Improves performance.
• Reduces network traffic.
• Supports code reuse.
• Enhances security.

9. What is a trigger?

A trigger is a special type of stored procedure that executes automatically when an INSERT, UPDATE or DELETE event occurs.

10. What is SQL Server Agent?

SQL Server Agent is a SQL Server service used to automate tasks such as scheduling jobs, running backups, executing scripts and sending alerts.

11. What is the Difference Between Login and User?

12. What is an index?

An index is a database object that improves the speed of data retrieval by creating a sorted structure on one or more columns.

13. What is the difference between Clustered and Non-Clustered Index?

Clustered Index and Non-Clustered Index are both indexing techniques in SQL Server, but they differ in how they store and organize data.

14. What is SCOPE_IDENTITY()?

SCOPE_IDENTITY() is a SQL Server function that returns the last identity value generated in the current session and scope. It is commonly used to retrieve the ID of the most recently inserted row.

15. What are transactions?

A transaction is a sequence of SQL statements executed as a single unit of work. It follows the ACID properties to ensure reliable data processing.

Intermediate-level Questions

16. What are the main components of SQL Server Architecture?

The main components of SQL Server Architecture are:

• Protocol Layer: Receives client requests.
• Relational Engine: Parses, optimizes and executes queries.
• Storage Engine: Manages data storage and retrieval.
• Buffer Manager: Caches data pages in memory for faster access.
• Database Files: Store database data and transaction logs.

17. How does SQL Server process a query?

When a query is executed, SQL Server first parses and validates it. The Query Optimizer creates an efficient execution plan and the Storage Engine retrieves or modifies the required data. Finally, the results are returned to the client.

18. Can SQL Server Agent run automatically?

Yes. SQL Server Agent can execute jobs automatically based on a defined schedule or when specific events occur.

Example:

The following query creates a schedule that runs every day at 2:00 AM.

USE msdb;
GO

EXEC sp_add_schedule
    @schedule_name = 'DailyBackupSchedule',
    @freq_type = 4,            -- Daily
    @freq_interval = 1,        -- Every day
    @active_start_time = 020000; -- 2:00 AM

• This schedule can then be attached to a SQL Server Agent job, allowing it to run automatically every day at 2:00 AM.

19. What is the purpose of SQL Server Configuration Manager?

It is used to:

• Start, stop or restart SQL Server services.
• Enable or disable network protocols such as TCP/IP and Named Pipes.
• Configure SQL Server service accounts.
• Manage client connectivity settings.

20. What are SQL Server User-Defined Functions (UDFs)?

A User-Defined Function (UDF) is a reusable database object in SQL Server that accepts input parameters, performs calculations or operations and returns a single value or a table. It helps simplify complex queries and promotes code reuse.

21. What are SQL Server Roles and Permissions?

SQL Server roles are collections of permissions that simplify user management. Permissions define the actions a user or role can perform on SQL Server objects, such as reading, modifying or deleting data.

22. When are triggers executed?

Triggers are executed automatically when an INSERT, UPDATE or DELETE operation occurs on a table or view. They are commonly used to enforce business rules, maintain data integrity and automatically perform actions in response to data changes.

23. When should Recursive CTEs be used?

Recursive CTEs should be used when working with hierarchical or recursive data, such as organizational structures, category trees or parent-child relationships.

Example:

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, EmployeeName
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName
    FROM Employees e
    INNER JOIN EmployeeHierarchy h
        ON e.ManagerID = h.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

24. What are the benefits of using MERGE?

The MERGE statement combines INSERT, UPDATE and DELETE operations into a single statement, simplifying data synchronization between two tables.

Example:

MERGE TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Name = S.Name
WHEN NOT MATCHED THEN
    INSERT (ID, Name)
    VALUES (S.ID, S.Name);

25. When is the OUTPUT clause used?

The OUTPUT clause is used to return the affected rows after an INSERT, UPDATE, DELETE or MERGE statement.

Example:

INSERT INTO Employees (EmployeeName)
OUTPUT INSERTED.EmployeeID
VALUES ('John');

26. Name some commonly used database objects in SQL Server.

Commonly used database objects in SQL Server include:

• Tables
• Views
• Stored Procedures
• User-Defined Functions (UDFs)
• Triggers
• Indexes
• Schemas
• Sequences

27. Why are execution plans important?

Execution plans show how SQL Server executes a query. They help identify performance bottlenecks and optimize queries for faster execution

28. How do SQL Server statistics improve query performance?

SQL Server statistics store information about the distribution of data in tables and indexes. The Query Optimizer uses this information to generate efficient execution plans and improve query performance.

29. What is the difference between a Temporary Table and a Table Variable?

30. What is the difference between a View and an Indexed View?

Advanced Level Interview Questions

31. Explain SQL Server Replication.

SQL Server Replication is a feature that copies and synchronizes data between multiple SQL Server databases. It helps improve data availability, supports distributed databases and keeps data consistent across different servers.

32. Why are Dynamic Management Views (DMVs) used?

Dynamic Management Views (DMVs) are used to monitor the health and performance of SQL Server. They provide information about query execution, server activity, memory usage and system performance, helping administrators troubleshoot and optimize SQL Server.

33. What is Parameter Sniffing?

Parameter Sniffing is a SQL Server feature in which the Query Optimizer uses the parameter values from the first execution of a stored procedure to generate and cache an execution plan.

It is used to:

• Improve query performance by reusing cached execution plans.
• Reduce the overhead of recompiling queries.
• Optimize query execution based on parameter values.

34. What is the difference between Estimated and Actual Execution Plans?

35. How do you identify and resolve blocking and deadlocks in SQL Server?

Blocking and deadlocks can be identified using SQL Server Management Studio (SSMS), Dynamic Management Views (DMVs) or Extended Events. They can be resolved by optimizing queries, keeping transactions short, creating appropriate indexes and accessing resources in a consistent order.

36. When would you use the CASE expression instead of multiple queries? Explain with an example.

The CASE expression is used to apply conditional logic within a single SQL query, making the query simpler and more efficient.

Example:

SELECT EmployeeName,
       Salary,
       CASE
           WHEN Salary >= 80000 THEN 'High'
           WHEN Salary >= 50000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryCategory
FROM Employees;

37. How do you return the affected rows after an UPDATE statement?

Use the OUTPUT clause.

UPDATE Employees
SET Salary = Salary + 5000
OUTPUT INSERTED.EmployeeID,
       INSERTED.Salary;

38. How do you generate sequential numbers without using an IDENTITY column?

Use a SEQUENCE object.

CREATE SEQUENCE EmpSeq
START WITH 1
INCREMENT BY 1;

SELECT NEXT VALUE FOR EmpSeq;

39. How do you retrieve the top 3 highest-paid employees from each department?

Use ROW_NUMBER() with a CTE.

WITH RankedEmployees AS
(
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RN
    FROM Employees
)
SELECT *
FROM RankedEmployees
WHERE RN <= 3;

40. How do you create an Indexed View in SQL Server?

An indexed view stores the query result physically on disk, which can improve the performance of complex queries. To create an indexed view, first create the view with SCHEMABINDING, then create a unique clustered index on it.

CREATE VIEW dbo.EmployeeSalarySummary
WITH SCHEMABINDING
AS
SELECT Department,
       COUNT_BIG(*) AS EmployeeCount,
       SUM(Salary) AS TotalSalary
FROM dbo.Employees
GROUP BY Department;
GO

CREATE UNIQUE CLUSTERED INDEX IX_EmployeeSalarySummary
ON dbo.EmployeeSalarySummary (Department);