10 TCS SQL Server Interview Questions and Answers – CLIMB

TCS SQL Server is a robust relational database management system widely used for enterprise-level data storage, management, and analysis. Known for its scalability, security features, and integration capabilities, TCS SQL Server supports a variety of applications, from small-scale projects to large, complex systems. Its comprehensive suite of tools and services makes it a preferred choice for organizations looking to manage their data efficiently.

This article aims to prepare you for interviews by providing a curated list of questions and answers related to TCS SQL Server. By familiarizing yourself with these topics, you will gain the confidence and knowledge needed to demonstrate your expertise and problem-solving abilities in a technical interview setting.

TCS SQL Server Interview Questions and Answers

1. Explain the ACID properties in the context of SQL Server.

ACID stands for Atomicity, Consistency, Isolation, and Durability, ensuring reliable database transactions and maintaining integrity.

• Atomicity: Ensures a transaction is treated as a single unit, either fully completing or failing. If any part fails, the entire transaction is rolled back, leaving the database unchanged. Managed using commands like BEGIN TRANSACTION, COMMIT, and ROLLBACK.
• Consistency: Ensures a transaction moves the database from one valid state to another, adhering to all rules and constraints.
• Isolation: Ensures operations of one transaction are isolated from others, preventing interference. SQL Server offers isolation levels like READ COMMITTED and SERIALIZABLE.
• Durability: Guarantees that once a transaction is committed, it remains so, even after a system failure, through the transaction log.

2. What is an index in SQL Server, and why is it important?

An index in SQL Server is a database object that enhances data retrieval speed at the cost of additional storage. Created on table columns, indexes allow the database engine to find rows efficiently. There are two main types: clustered and non-clustered.

• A clustered index determines the physical order of data in a table, with only one allowed per table.
• A non-clustered index creates a separate structure pointing to the original data rows, allowing multiple per table.

Indexes significantly improve query performance, especially in large databases, by avoiding full table scans.

Example:

-- Creating a clustered index on the 'ID' column
CREATE CLUSTERED INDEX idx_id ON Employees(ID);

-- Creating a non-clustered index on the 'LastName' column
CREATE NONCLUSTERED INDEX idx_lastname ON Employees(LastName);

3. Write a SQL query using a subquery to find employees who earn more than the average salary in the ‘Employees’ table.

To find employees earning more than the average salary in the ‘Employees’ table, use a subquery to calculate the average salary and compare each employee’s salary to it.

Example:

SELECT EmployeeID, EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

The subquery calculates the average salary, and the main query selects employees with salaries above this average.

4. What is a stored procedure, and what are its advantages?

A stored procedure in SQL Server is a set of SQL statements executed as a single unit, stored in the database for reuse.

Advantages include:

• Performance: Precompiled, reducing execution time.
• Reusability: Can be reused across applications.
• Security: Restricts direct table access, with permissions granted on procedures.
• Maintainability: Logic changes in one place without affecting application code.
• Modularity: Allows for modular programming, simplifying management.

Example:

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

5. Write a SQL query to calculate the total sales amount for each customer using the ‘Orders’ table.

To calculate the total sales amount for each customer using the ‘Orders’ table, use the GROUP BY clause with the SUM() function.

SELECT CustomerID, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY CustomerID;

6. Write a SQL query using a window function to rank employees based on their salaries within each department.

Window functions in SQL Server perform calculations across related table rows. Use the RANK() function to rank employees by salary within each department.

Example:

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

The RANK() function assigns a rank to each employee within their department based on salary.

7. What are some common performance tuning techniques in SQL Server?

Performance tuning in SQL Server involves optimizing queries and the database. Common techniques include:

• Indexing: Enhances query performance by reducing data scans.
• Query Optimization: Rewriting queries for efficiency and using execution plans.
• Statistics: Keeping statistics up-to-date for optimal execution plans.
• Partitioning: Improves performance and manageability of large tables.
• Database Configuration: Optimizing settings like memory allocation and file placement.
• Monitoring and Profiling: Using tools to identify performance issues.
• Concurrency Control: Managing concurrent data access without degradation.
• Hardware Resources: Ensuring adequate resources for workload efficiency.

8. Write a SQL query using a recursive CTE to display an organizational hierarchy from the ‘Employees’ table.

A recursive Common Table Expression (CTE) in SQL Server queries hierarchical data, like organizational structures. It allows recursive queries to retrieve data hierarchically.

Example:

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

    UNION ALL

    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Level + 1
    FROM 
        Employees e
    INNER JOIN 
        EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    EmployeeName,
    ManagerID,
    Level
FROM 
    EmployeeHierarchy
ORDER BY 
    Level, EmployeeID;

9. Explain how error handling works in SQL Server, including TRY…CATCH blocks.

In SQL Server, error handling uses TRY…CATCH blocks. If an error occurs in the TRY block, control transfers to the CATCH block for handling.

Example:

BEGIN TRY
    -- Start a transaction
    BEGIN TRANSACTION

    -- Execute some SQL statements
    INSERT INTO Employees (Name, Position) VALUES ('John Doe', 'Developer')

    -- Commit the transaction
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- Rollback the transaction if an error occurs
    ROLLBACK TRANSACTION

    -- Retrieve error information
    DECLARE @ErrorMessage NVARCHAR(4000)
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE()

    -- Print the error information
    PRINT 'Error: ' + @ErrorMessage
    PRINT 'Severity: ' + CAST(@ErrorSeverity AS NVARCHAR)
    PRINT 'State: ' + CAST(@ErrorState AS NVARCHAR)
END CATCH

If an error occurs, the transaction is rolled back, and error information is retrieved and printed.

10. Describe the process of backing up and restoring a database in SQL Server.

Backing up and restoring a database in SQL Server involves creating a copy (backup) and using it to recreate the database (restore) if needed.

Types of backups:

• Full Backup: A complete copy of the database.
• Differential Backup: Captures data changes since the last full backup.
• Transaction Log Backup: Backs up the transaction log since the last log backup.

Example of a full backup:

BACKUP DATABASE [YourDatabaseName] 
TO DISK = 'C:\Backups\YourDatabaseName.bak'
WITH INIT;

Example of restoring a database:

RESTORE DATABASE [YourDatabaseName] 
FROM DISK = 'C:\Backups\YourDatabaseName.bak'
WITH REPLACE;