15 MS SQL Server Interview Questions and Answers – CLIMB

MS SQL Server is a robust relational database management system developed by Microsoft. It is widely used in various industries for managing and storing data, offering high performance, scalability, and security. With features like advanced analytics, in-memory performance, and data integration, MS SQL Server is a critical tool for businesses that rely on data-driven decision-making.

This article provides a curated selection of interview questions designed to test your knowledge and proficiency with MS SQL Server. By reviewing these questions and their detailed answers, you will be better prepared to demonstrate your expertise and problem-solving abilities in a technical interview setting.

MS SQL Server Interview Questions and Answers

1. Explain the concept of Normalization and its different forms.

Normalization is the process of structuring a relational database to reduce redundancy and improve data integrity. It involves decomposing a table into smaller tables and defining relationships between them. The different normal forms are:

• First Normal Form (1NF): Ensures that the table has a primary key and that all columns contain atomic values.
• Second Normal Form (2NF): Achieved when the table is in 1NF and all non-key attributes are fully functionally dependent on the primary key.
• Third Normal Form (3NF): Achieved when the table is in 2NF and all the attributes are functionally dependent only on the primary key.
• Boyce-Codd Normal Form (BCNF): A stricter version of 3NF. A table is in BCNF if it is in 3NF and for every functional dependency (A -> B), A should be a super key.
• Fourth Normal Form (4NF): Achieved when the table is in BCNF and has no multi-valued dependencies.
• Fifth Normal Form (5NF): Achieved when the table is in 4NF and cannot be decomposed into any smaller tables without losing data or introducing redundancy.

2. Write a query to find the second highest salary from an Employee table.

To find the second highest salary from an Employee table, you can use a subquery along with the DISTINCT and ORDER BY clauses:

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Alternatively, use the DENSE_RANK() function:

SELECT Salary AS SecondHighestSalary
FROM (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rank
    FROM Employee
) AS ranked_salaries
WHERE rank = 2;

3. Describe the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

In SQL Server, joins combine rows from two or more tables based on a related column. The main types are:

• INNER JOIN: Returns only the rows with matching values in both tables.
• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
• RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
• FULL OUTER JOIN: Returns all rows when there is a match in either left or right table. Rows that do not have a match in one of the tables will have NULL values for columns from that table.

Example:

CREATE TABLE Employees (
    EmployeeID int,
    Name varchar(255)
);

CREATE TABLE Departments (
    DepartmentID int,
    DepartmentName varchar(255),
    EmployeeID int
);

-- INNER JOIN
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.EmployeeID = Departments.EmployeeID;

-- LEFT JOIN
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.EmployeeID = Departments.EmployeeID;

-- RIGHT JOIN
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.EmployeeID = Departments.EmployeeID;

-- FULL OUTER JOIN
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.EmployeeID = Departments.EmployeeID;

4. What is a CTE (Common Table Expression) and how do you use it?

A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs are useful for breaking down complex queries into simpler parts and for creating 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;

5. Explain the concept of Transactions and how to implement them in SQL Server.

A transaction in SQL Server is a sequence of operations performed as a single logical unit of work. If any operation fails, the entire transaction is rolled back. If all succeed, the transaction is committed.

To implement transactions:

• BEGIN TRANSACTION: Marks the starting point of an explicit transaction.
• COMMIT TRANSACTION: Saves all changes made during the transaction.
• ROLLBACK TRANSACTION: Undoes all changes made during the transaction.

Example:

BEGIN TRANSACTION;

BEGIN TRY
    -- Insert operation
    INSERT INTO Employees (Name, Position) VALUES ('John Doe', 'Developer');

    -- Update operation
    UPDATE Employees SET Position = 'Senior Developer' WHERE Name = 'John Doe';

    -- Commit the transaction if all operations succeed
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Rollback the transaction if any operation fails
    ROLLBACK TRANSACTION;

    -- Optionally, handle the error
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    PRINT @ErrorMessage;
END CATCH;

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

Pivoting data in SQL Server transforms rows into columns, making it easier to analyze and report data. This is often achieved using the PIVOT function.

Example:

SELECT 
    ProductID,
    [2021] AS Sales_2021,
    [2022] AS Sales_2022
FROM 
    (SELECT ProductID, Year, SalesAmount
     FROM SalesData) AS SourceTable
PIVOT
    (SUM(SalesAmount)
     FOR Year IN ([2021], [2022])) AS PivotTable;

7. How do you implement Row-Level Security in SQL Server?

Row-Level Security (RLS) in SQL Server controls access to rows in a database table based on user characteristics. This is useful for applications where different users should only see their own data.

To implement RLS, create a security policy and a predicate function that defines the access logic.

-- Step 1: Create a predicate function
CREATE FUNCTION dbo.fn_securitypredicate(@UserID AS int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @UserID = USER_ID();

-- Step 2: Create a security policy
CREATE SECURITY POLICY dbo.SecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(UserID) ON dbo.YourTable,
ADD BLOCK PREDICATE dbo.fn_securitypredicate(UserID) ON dbo.YourTable
WITH (STATE = ON);

8. Explain the use of Window Functions and provide an example.

Window functions in SQL Server perform calculations across a set of rows related to the current row. They allow you to add calculated columns while retaining the original row structure.

Example:

SELECT 
    EmployeeID,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM 
    Employees;

9. How do you optimize a slow-running query?

To optimize a slow-running query in SQL Server, consider these strategies:

• Indexing: Ensure appropriate indexes are in place to speed up data retrieval.
• Query Rewriting: Rewrite queries for efficiency, such as using joins instead of subqueries.
• Execution Plans: Analyze execution plans to identify bottlenecks.
• Statistics: Keep statistics up-to-date for efficient query execution plans.
• Partitioning: Consider partitioning large tables to improve performance.
• Avoiding Cursors: Use set-based operations instead of cursors.
• Temp Tables and Table Variables: Use judiciously to manage overhead.
• Hardware Resources: Ensure adequate hardware resources for the workload.

10. Write a query to delete duplicate rows from a table.

To delete duplicate rows from a table in SQL Server, use a common table expression (CTE) with the ROW_NUMBER() function to identify duplicates and then delete them.

Example:

WITH CTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT NULL)) AS row_num
    FROM 
        your_table
)
DELETE FROM CTE WHERE row_num > 1;

11. How do you implement Full-Text Search in SQL Server?

Full-Text Search (FTS) in SQL Server allows for efficient querying of text data. It supports complex queries, including searching for words, phrases, and word proximity.

To implement Full-Text Search:

• Create a Full-Text Catalog.
• Create a Full-Text Index on the desired table and columns.
• Perform Full-Text Search queries using the CONTAINS or FREETEXT predicates.

Example:

-- Step 2: Create a Full-Text Catalog
CREATE FULLTEXT CATALOG MyFullTextCatalog AS DEFAULT;

-- Step 3: Create a Full-Text Index on the desired table and columns
CREATE FULLTEXT INDEX ON Products (ProductName, ProductDescription)
    KEY INDEX PK_Products
    ON MyFullTextCatalog;

-- Step 4: Perform Full-Text Search queries
SELECT ProductID, ProductName
FROM Products
WHERE CONTAINS(ProductDescription, 'search term');

12. What are stored procedures and how do they improve performance and security?

Stored procedures in SQL Server are precompiled collections of SQL statements stored under a name and processed as a unit. They perform operations such as data manipulation and complex business logic. Stored procedures can accept input parameters, return output parameters, and provide a way to control access to data.

Stored procedures improve performance by reducing the amount of information sent between the client and the server. Since they are precompiled, the execution plan is cached and reused, which reduces the overhead of query parsing and optimization.

Example:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID
END

13. Discuss various performance tuning techniques in SQL Server.

Performance tuning in SQL Server involves techniques aimed at optimizing query performance and the database as a whole. Key techniques include:

• Indexing: Use clustered and non-clustered indexes to speed up data retrieval.
• Query Optimization: Analyze and rewrite queries for efficiency.
• Statistics: Keep statistics up-to-date for efficient query execution plans.
• Partitioning: Partition large tables to improve performance and manageability.
• TempDB Optimization: Configure multiple data files and place them on fast storage.
• Memory Management: Configure SQL Server memory settings appropriately.
• Concurrency Control: Use appropriate isolation levels to balance data consistency and performance.
• Database Maintenance: Regularly perform tasks such as index rebuilding and updating statistics.
• Monitoring and Profiling: Use tools to identify performance issues and monitor database health.

14. Explain the importance of security in SQL Server and some common practices to ensure it.

Security in SQL Server is important to protect data, ensure integrity, and prevent unauthorized access. Common practices include:

• Authentication and Authorization: Use Windows Authentication and implement role-based access control.
• Encryption: Encrypt data at rest and in transit using TDE and SSL.
• Auditing and Monitoring: Enable SQL Server Audit to track and log events.
• Regular Updates and Patching: Keep SQL Server up to date with security patches.
• Backup and Recovery: Implement a robust backup and recovery strategy.
• Least Privilege Principle: Grant users the minimum level of access required.

15. How do you ensure data integrity in SQL Server?

Ensuring data integrity in SQL Server involves several mechanisms:

1. Constraints: Rules enforced on data columns to ensure accuracy and reliability. Types include:

• Primary Key Constraint: Ensures each row in a table is unique and not null.
• Foreign Key Constraint: Ensures values match values in another table, maintaining referential integrity.
• Unique Constraint: Ensures all values in a column are unique.
• Check Constraint: Ensures all values in a column satisfy a specific condition.
• Default Constraint: Provides a default value for a column when none is specified.

2. Transactions: Ensure a series of operations are completed successfully before committing changes. If any operation fails, the entire transaction is rolled back.

Example:

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
END
ELSE
BEGIN
    COMMIT TRANSACTION;
END

3. Triggers: Special types of stored procedures that automatically execute when certain events occur, such as insert, update, or delete operations.

Example:

CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    -- Business logic to enforce data integrity
END