15 MS SQL Interview Questions and Answers

MS SQL, or Microsoft SQL Server, is a robust relational database management system widely used for storing and retrieving data as requested by other software applications. Known for its scalability, security features, and integration capabilities, MS SQL is a critical tool for managing large volumes of data efficiently. Its comprehensive suite of tools and services makes it a preferred choice for businesses of all sizes, from small startups to large enterprises.

This article aims to prepare you for your upcoming interview by providing a curated list of MS SQL questions and answers. By familiarizing yourself with these questions, you will gain a deeper understanding of key concepts and practical applications, enhancing your ability to demonstrate your expertise and problem-solving skills during the interview process.

MS SQL Interview Questions and Answers

1. What is a Primary Key and how do you define it in a table?

A primary key uniquely identifies a record in a database table, ensuring no duplicate records exist. It can be a single column or a combination of columns (composite key) and is essential for data integrity and establishing relationships between tables.

To define a primary key in a table, use the following SQL syntax:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50)
);

In this example, EmployeeID is the primary key for the Employees table, ensuring uniqueness and non-nullability.

You can also define a composite primary key:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Here, the combination of OrderID and ProductID serves as the primary key for the Orders table.

2. How would you retrieve the top 5 highest salaries from an Employee table?

To retrieve the top 5 highest salaries from an Employee table, use the ORDER BY clause with the TOP keyword. This sorts salaries in descending order and limits the result to the top 5 entries.

Example:

SELECT TOP 5 Salary
FROM Employee
ORDER BY Salary DESC;

This query selects the top 5 salaries by ordering them in descending order.

3. Explain the difference between INNER JOIN and LEFT JOIN. Provide an example query for each.

An INNER JOIN returns rows with matching values in both tables, useful for retrieving records with corresponding entries in both tables.

A LEFT JOIN returns all rows from the left table and matched rows from the right table. If no match exists, the result is NULL on the right side. This is useful for retrieving all records from the left table, regardless of matches in the right table.

Example of INNER JOIN:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

Example of LEFT JOIN:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

4. What are indexes and how do they improve query performance?

Indexes are data structures that speed up row retrieval. They work like a book index, allowing quick data location without scanning the entire table. Types include clustered and non-clustered indexes.

• A clustered index determines the physical order of data in a table. Each table can have only one clustered index.
• A non-clustered index creates a separate structure with pointers to the data. A table can have multiple non-clustered indexes.

Indexes improve query performance by reducing the data scanned. The database engine uses the index to quickly locate rows matching query conditions.

5. Explain the concept of normalization and its importance.

Normalization structures a database to reduce redundancy and improve data integrity. It divides large tables into smaller ones and defines relationships between them, guided by normal forms.

Common normal forms include:

• First Normal Form (1NF): Ensures a primary key and atomic values, eliminating repeating groups.
• Second Normal Form (2NF): Ensures non-key attributes are fully dependent on the primary key, eliminating partial dependencies.
• Third Normal Form (3NF): Ensures attributes depend only on the primary key, eliminating transitive dependencies.
• Boyce-Codd Normal Form (BCNF): Ensures every determinant is a candidate key.

Normalization enhances data integrity, efficiency, consistency, and scalability.

6. How can you optimize a slow-running query?

Optimizing a slow-running query involves several strategies:

• Indexing: Create appropriate indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
• Query Refactoring: Rewrite queries for efficiency, simplifying complex joins and using subqueries.
• Execution Plan Analysis: Use SQL Server Management Studio (SSMS) to analyze execution plans and identify bottlenecks.
• Statistics Update: Ensure table statistics are up-to-date to avoid suboptimal query plans.
• Avoiding Cursors: Use set-based operations instead of resource-intensive cursors.
• Limiting Data Retrieval: Retrieve only necessary columns and rows using SELECT and WHERE clauses.
• Temp Tables and Table Variables: Use temporary tables or table variables for intermediate results.
• Partitioning: Consider partitioning large tables to reduce data scanned.
• Proper Use of Joins: Ensure efficient use of joins and proper indexing of join conditions.
• Database Configuration: Optimize settings like memory allocation and disk I/O.

7. Write a query to pivot data from rows to columns.

Pivoting data transforms it from rows to columns, often for reporting. In MS SQL, the PIVOT operator achieves this transformation.

Example:

Suppose we have a table named Sales:

CREATE TABLE Sales (
    Product VARCHAR(50),
    Year INT,
    Amount INT
);

INSERT INTO Sales (Product, Year, Amount) VALUES
('ProductA', 2020, 100),
('ProductA', 2021, 150),
('ProductB', 2020, 200),
('ProductB', 2021, 250);

To pivot this data:

SELECT Product, [2020] AS Year2020, [2021] AS Year2021
FROM (
    SELECT Product, Year, Amount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Amount)
    FOR Year IN ([2020], [2021])
) AS PivotTable;

This query transforms the data into:

Product	Year2020	Year2021
ProductA	100	150
ProductB	200	250

8. What are Common Table Expressions (CTEs) and how do you use them?

Common Table Expressions (CTEs) are temporary result sets referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They simplify complex queries, improve readability, and manage recursive queries.

Example:

WITH Sales_CTE (SalesPerson, TotalSales) AS (
    SELECT SalesPerson, SUM(SalesAmount)
    FROM Sales
    GROUP BY SalesPerson
)
SELECT SalesPerson, TotalSales
FROM Sales_CTE
WHERE TotalSales > 10000;

In this example, the CTE calculates total sales for each salesperson, and the main query selects those with total sales over 10,000.

9. Explain the ACID properties in the context of transactions.

ACID properties ensure database transactions are processed reliably:

1. Atomicity: Ensures all operations within a transaction are completed successfully. If any operation fails, the entire transaction is rolled back.
2. Consistency: Ensures a transaction brings the database from one valid state to another, maintaining data validity.
3. Isolation: Ensures transactions are executed in isolation, preventing interference and data anomalies.
4. Durability: Ensures committed transactions remain so, even in the event of a system failure.

10. How would you implement a recursive query?

A recursive query in MS SQL is implemented using a Common Table Expression (CTE). Recursive CTEs are useful for querying hierarchical data, such as organizational charts.

A recursive CTE consists of:

• An initial query forming the base result set.
• A recursive query referencing the CTE itself.
• A termination condition to stop recursion.

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 eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

This example retrieves all levels of an employee hierarchy.

11. What is a clustered index and how does it differ from a non-clustered index?

A clustered index sorts and stores data rows based on key values, determining the physical order of data. Each table can have only one clustered index.

A non-clustered index creates a separate structure with pointers to data rows, without altering their physical order. A table can have multiple non-clustered indexes.

Key differences:

• Physical Order: Clustered indexes sort and store data rows, while non-clustered indexes do not.
• Number of Indexes: A table can have one clustered index but multiple non-clustered indexes.
• Performance: Clustered indexes are generally faster for data retrieval, while non-clustered indexes may require additional lookups.

12. How do you handle concurrency issues in SQL Server?

Concurrency issues in SQL Server arise when multiple transactions execute simultaneously, potentially leading to conflicts. SQL Server provides mechanisms to handle these issues:

• Locking: Controls data access by applying locks at different levels, ensuring only one transaction can modify data at a time.
• Isolation Levels: Control the visibility of changes made by one transaction to others, balancing consistency and concurrency.
• Optimistic Concurrency Control: Assumes conflicts are rare, allowing transactions to proceed without locking resources, checking for conflicts before committing.
• Deadlock Detection and Resolution: Automatically detects and resolves deadlocks by terminating one of the conflicting transactions.
• Row Versioning: Maintains multiple versions of a row, allowing transactions to read a consistent snapshot without blocking others.

13. Describe the various transaction isolation levels in SQL Server and their impact on data consistency.

Transaction isolation levels in SQL Server determine how transaction integrity is maintained and data is accessed concurrently:

1. Read Uncommitted: Allows reading uncommitted data, resulting in dirty reads.
2. Read Committed: Ensures reading only committed data, preventing dirty reads but allowing non-repeatable reads.
3. Repeatable Read: Prevents dirty and non-repeatable reads but allows phantom reads.
4. Serializable: Ensures complete isolation, preventing dirty, non-repeatable, and phantom reads, but can impact performance.

14. Explain the process of performance tuning in SQL Server.

Performance tuning in SQL Server involves strategies to ensure efficient database operation:

• Index Optimization: Create, modify, and delete indexes based on query patterns.
• Query Optimization: Write efficient SQL queries using appropriate joins and subqueries.
• Execution Plans: Analyze execution plans to identify performance bottlenecks.
• Statistics: Keep statistics up-to-date for accurate query optimization.
• Hardware Resources: Ensure adequate CPU, memory, and disk I/O capacity.
• Database Configuration: Optimize settings like max degree of parallelism and memory allocation.
• Monitoring and Profiling: Use tools like SQL Server Profiler and Performance Monitor to track performance metrics.

15. Describe the backup and recovery options available in SQL Server and their importance.

SQL Server provides several backup and recovery options to ensure data integrity and availability:

• Full Backup: Captures the entire database, including all data and objects.
• Differential Backup: Captures only data changed since the last full backup.
• Transaction Log Backup: Captures all transactions since the last transaction log backup, essential for point-in-time recovery.
• File and Filegroup Backup: Allows backing up individual files or filegroups within a database.
• Copy-Only Backup: A special backup that does not interfere with the regular backup sequence.

These options provide a comprehensive recovery strategy, enabling data restoration to a specific point in time and minimizing data loss and downtime.