Top 50 T-SQL Interview Questions and Answers (2026)

By : Fiona Brown Fiona Brown
Hours Updated

T-SQL Interview Questions and Answers

Preparing for a T-SQL interview? Time to sharpen your understanding of how databases truly work beneath the surface. With T-SQL interview questions, recruiters assess not just syntax recall but your command over data manipulation, optimization, and logical structuring.

Opportunities in this domain continue to expand as businesses depend on data-driven insights. Candidates with strong technical expertise, analysis skills, and real-world problem-solving abilities stand out—whether freshers or professionals with 5 to 10 years of experience. Understanding questions and answers at both basic and advanced levels helps team leaders, managers, and technical seniors identify strong SQL skillsets and root-level experience.

Our guide draws on insights from over 65 hiring managers, 40+ senior developers, and data professionals across industries, ensuring coverage from foundational SQL logic to advanced optimization techniques trusted by technical leaders worldwide. Read more…

👉 Free PDF Download: T-SQL Interview Questions & Answers

Top T-SQL Interview Questions and Answers

1) What is T-SQL, and how does it differ from standard SQL?

Transact-SQL (T-SQL) is Microsoft’s proprietary extension of the SQL language, primarily used with Microsoft SQL Server. It enhances standard SQL by introducing procedural programming features such as variables, conditions, loops, error handling, and built-in functions. While standard SQL focuses on data manipulation (SELECT, INSERT, UPDATE, DELETE), T-SQL supports control-of-flow statements (IF…ELSE, WHILE), transaction handling, and system functions that allow developers to write complex scripts.

Aspect SQL T-SQL
Ownership ANSI/ISO Standard Microsoft
Procedural Logic Limited Supported (variables, loops)
Error Handling Minimal TRY…CATCH supported
Primary Use Generic DBs SQL Server

Example:

DECLARE @count INT = 5;
WHILE @count > 0
BEGIN
    PRINT @count;
    SET @count -= 1;
END;

2) Explain the different types of joins in T-SQL with examples.

Joins in T-SQL combine rows from two or more tables based on related columns. Understanding their types is crucial for relational data queries.

Join Type Description Example Syntax
INNER JOIN Returns only matching rows SELECT * FROM A INNER JOIN B ON A.id = B.id;
LEFT JOIN All from left + matches from right SELECT * FROM A LEFT JOIN B ON A.id = B.id;
RIGHT JOIN All from right + matches from left SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
FULL JOIN Combines LEFT + RIGHT SELECT * FROM A FULL JOIN B ON A.id = B.id;
CROSS JOIN Cartesian product SELECT * FROM A CROSS JOIN B;

Practical Example: Joining Orders and Customers to find which customers have placed orders using INNER JOIN.

3) What are Common Table Expressions (CTEs), and what are their advantages?

A Common Table Expression (CTE) provides a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves readability and simplifies recursive queries.

Advantages:

  • Enhances query clarity and maintenance.
  • Enables recursion (for hierarchical data like org charts).
  • Helps avoid repeated subqueries.
  • Increases modularity in large scripts.

Example:

WITH EmployeeCTE AS (
    SELECT EmpID, EmpName, ManagerID
    FROM Employees
)
SELECT * FROM EmployeeCTE WHERE ManagerID IS NULL;

4) How do temporary tables and table variables differ in T-SQL?

Both are used to store intermediate results, but their behavior and scope differ significantly.

Feature Temporary Table (#Temp) Table Variable (@TableVar)
Stored In TempDB Memory (with limited TempDB usage)
Transactional Scope Follows transactions Independent of transactions
Indexes Supported Limited
Performance Better for large datasets Better for small datasets

Example:

DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar VALUES (1, 'Alice');

Use temporary tables when working with large datasets or requiring indexes.

5) Explain the concept of transactions in T-SQL and their lifecycle.

A transaction in T-SQL ensures that a sequence of operations executes as a single logical unit. The lifecycle includes BEGIN TRANSACTION, COMMIT, and ROLLBACK.

Stage Description
BEGIN TRANSACTION Starts the transaction
COMMIT TRANSACTION Saves all changes permanently
ROLLBACK TRANSACTION Undoes all operations since the last BEGIN

Example:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccID = 2;
COMMIT TRANSACTION;

If an error occurs mid-way, ROLLBACK maintains data integrity.

6) What is the difference between DELETE, TRUNCATE, and DROP commands?

Command Function Rollback Affects Structure Speed
DELETE Removes specific rows Yes No Slower
TRUNCATE Removes all rows No (usually) No Fast
DROP Deletes entire table No Yes Fastest

Example:

DELETE FROM Employees WHERE Department = 'HR';
TRUNCATE TABLE TempData;
DROP TABLE OldLogs;

Use DELETE for selective removal, TRUNCATE for clearing, and DROP to remove the table completely.

RELATED ARTICLES

7) How does error handling work in T-SQL?

T-SQL provides structured error handling through the TRY...CATCH block, allowing developers to gracefully manage runtime errors.

Example:

BEGIN TRY
    INSERT INTO Employees VALUES (1, 'John');
END TRY
BEGIN CATCH
    PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;

This approach isolates the faulty operation and prevents the transaction from corrupting data integrity. Developers can also access system functions like ERROR_NUMBER() or ERROR_SEVERITY() for diagnostics.

8) What are the different ways to optimize T-SQL query performance?

Optimization involves fine-tuning SQL design, indexing, and execution strategies.

Key Techniques:

  1. Use proper indexing on frequently queried columns.
  2. Avoid SELECT * — explicitly specify columns.
  3. Use set-based operations instead of cursors.
  4. Analyze execution plans using SQL Server Management Studio.
  5. Use JOINs efficiently with proper ON conditions.
  6. Reduce nested subqueries; prefer CTEs or temp tables.

Performance tuning in T-SQL also includes monitoring query execution statistics using SET STATISTICS IO ON.

9) What are window functions, and when should you use them?

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single result. They are useful for ranking, running totals, and moving averages.

Example:

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

Common functions include RANK(), ROW_NUMBER(), DENSE_RANK(), and SUM() OVER().

These are crucial for analytical workloads where you need both aggregated and row-level data.

10) Explain the difference between clustered and non-clustered indexes.

Feature Clustered Index Non-Clustered Index
Data Storage Physically rearranges table Separate structure
Number per Table One Multiple
Performance Faster for range queries Faster for specific lookups
Usage Example Primary key Secondary searches

Example:

CREATE CLUSTERED INDEX IX_EmployeeID ON Employees(EmployeeID);
CREATE NONCLUSTERED INDEX IX_Dept ON Employees(Department);

Choosing the right index type directly affects query execution speed and storage efficiency.

11) What are stored procedures in T-SQL and why are they used?

A stored procedure is a precompiled collection of one or more SQL statements stored on the server. They enhance performance, security, and reusability by allowing you to encapsulate logic and execute it repeatedly without recompiling. Stored procedures reduce network traffic and support parameters for dynamic execution.

Example:

CREATE PROCEDURE GetEmployeeDetails @Dept NVARCHAR(50)
AS
BEGIN
    SELECT EmpName, Position FROM Employees WHERE Department = @Dept;
END;

Benefits:

  • Better performance due to precompilation.
  • Improved security through controlled execution.
  • Easier code maintenance and modularization.

12) Explain the difference between a stored procedure and a function in T-SQL.

Aspect Stored Procedure Function
Return Type Can return multiple values Must return a single value or table
Use in SELECT Not allowed Allowed
Error Handling TRY…CATCH supported Limited
Execution Executed via EXEC Used inline with SQL
Transaction Control Supported Not supported

Example:

  • Procedure: EXEC GetEmployeeDetails 'HR';
  • Function: SELECT dbo.GetSalary(101);

Functions are ideal for calculations; procedures are better for business logic and data manipulation.

13) What is a trigger in T-SQL, and what are its types?

A trigger is a special stored procedure that executes automatically in response to certain events (INSERT, UPDATE, DELETE) on a table or view. Triggers are used for enforcing business rules, auditing changes, or maintaining referential integrity.

Type Description
AFTER Trigger Fires after the event completes
INSTEAD OF Trigger Executes in place of the triggering event

Example:

CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    PRINT 'New employee record added!';
END;

Avoid overusing triggers — they can impact performance and complicate debugging.

14) How do you handle NULL values in T-SQL?

NULL represents missing or unknown data. T-SQL provides several functions to handle it effectively:

  • ISNULL(expression, replacement) → replaces NULL with a default.
  • COALESCE(expression1, expression2, ...) → returns first non-NULL value.
  • NULLIF(expression1, expression2) → returns NULL if expressions are equal.

Example:

SELECT ISNULL(Manager, 'No Manager') AS ManagerName FROM Employees;

Best practice: Always account for NULLs in joins and conditions to avoid unexpected results.

15) What are cursors in T-SQL and when should they be avoided?

A cursor allows row-by-row processing of query results — useful for complex logic where set-based operations are insufficient. However, cursors are slow and resource-heavy compared to set-based alternatives.

Example:

DECLARE emp_cursor CURSOR FOR SELECT EmpName FROM Employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor;
-- process
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Disadvantages:

  • Increased memory usage.
  • Poor scalability.
  • Reduced performance.

Alternative: Use table joins, subqueries, or window functions whenever possible.

16) Explain the MERGE statement and its use cases.

The MERGE statement performs INSERT, UPDATE, and DELETE operations in a single command — ideal for synchronizing two tables.

Syntax Example:

MERGE INTO Target AS T
USING Source AS S
ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET T.Name = S.Name
WHEN NOT MATCHED BY TARGET THEN INSERT (ID, Name) VALUES (S.ID, S.Name)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Use Cases:

  • Data warehousing (sync staging and target tables).
  • Incremental data loading.
  • Maintaining audit or dimension tables.

17) What are the different types of user-defined functions (UDFs) in T-SQL?

Type Description Example
Scalar Returns a single value CREATE FUNCTION GetTax(@Salary DECIMAL) RETURNS DECIMAL
Inline Table-Valued Returns a table via single SELECT RETURN SELECT * FROM Employees WHERE Dept = 'HR'
Multi-Statement Table-Valued Returns a table after multiple statements Useful for complex logic

Functions promote code reuse and enhance modular query design.

They should be deterministic (return the same result for the same input) whenever possible for performance optimization.

18) What is normalization, and what are its advantages and disadvantages?

Normalization is the process of organizing data in a database to minimize redundancy and improve integrity. It involves dividing tables into smaller, related entities.

Normal Form Rule Example
1NF Eliminate repeating groups Split comma-separated data
2NF Remove partial dependencies Ensure full dependency on primary key
3NF Remove transitive dependencies Move derived attributes

Advantages:

  • Reduces redundancy.
  • Ensures data consistency.
  • Simplifies maintenance.

Disadvantages:

  • Complex joins.
  • Potential performance trade-offs for analytical queries.

19) What are the different types of constraints in T-SQL?

Constraints enforce rules on data integrity within a table.

Constraint Purpose Example
PRIMARY KEY Uniquely identifies each row PRIMARY KEY (EmpID)
FOREIGN KEY Links two tables FOREIGN KEY (DeptID)
UNIQUE Ensures unique column values UNIQUE (Email)
CHECK Validates data range CHECK (Age >= 18)
DEFAULT Provides default values DEFAULT GETDATE()

Constraints ensure accuracy and reliability, reducing the need for extensive application-level validation.

20) How do you manage permissions and security in T-SQL?

T-SQL manages database security via logins, users, roles, and permissions.

Permissions can be granted or revoked at object or schema levels.

Example:

CREATE LOGIN John WITH PASSWORD = 'Strong@123';
CREATE USER John FOR LOGIN John;
GRANT SELECT, INSERT ON Employees TO John;

Best Practices:

  • Use roles instead of direct user permissions.
  • Avoid using sa or system accounts for applications.
  • Regularly audit permissions with sys.database_permissions.

Proper permission management ensures principle of least privilege and compliance with security policies.

21) What are the different transaction isolation levels in T-SQL?

Transaction isolation levels determine how one transaction is isolated from others — balancing consistency with concurrency. SQL Server supports the following:

Isolation Level Description Dirty Read Non-repeatable Read Phantom Read
READ UNCOMMITTED Reads uncommitted data Yes Yes Yes
READ COMMITTED Default; locks prevent dirty reads No Yes Yes
REPEATABLE READ Prevents data change until commit No No Yes
SERIALIZABLE Full isolation; highest locking No No No
SNAPSHOT Uses versioning, not locking No No No

Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- your code
COMMIT;

Use SNAPSHOT for high concurrency systems to minimize blocking without sacrificing consistency.

22) What is a deadlock in SQL Server, and how can you prevent it?

A deadlock occurs when two transactions hold locks that each other needs, resulting in a standoff. SQL Server automatically detects and terminates one transaction as the deadlock victim.

Example Scenario:

  • Transaction A locks Table1 then waits for Table2.
  • Transaction B locks Table2 then waits for Table1.

Prevention Techniques:

  1. Access resources in the same order.
  2. Keep transactions short.
  3. Use appropriate isolation levels.
  4. Avoid user interaction inside transactions.

Use SQL Profiler or Extended Events to trace deadlocks in real time.

23) Explain the difference between pessimistic and optimistic concurrency control.

Type Description Locking Mechanism Use Case
Pessimistic Locks data during transaction Heavy locking High conflict environments
Optimistic Uses row versioning, checks before commit Minimal locking Read-heavy, low-conflict workloads

Example:

  • Pessimistic: Default READ COMMITTED locking.
  • Optimistic: SNAPSHOT isolation with row versioning.

Optimistic concurrency enhances performance for systems with large read operations and infrequent updates.

24) How can you analyze and optimize a slow-running T-SQL query?

  1. Check Execution Plan: Identify scans, missing indexes, and costly operations.
  2. Use SET STATISTICS IO/TIME: Analyze I/O and CPU usage.
  3. Avoid cursors and loops: Replace with set-based operations.
  4. Index optimization: Add or reorganize fragmented indexes.
  5. Parameter sniffing: Use OPTION (RECOMPILE) to generate new plans.

Example:

SET STATISTICS TIME ON;
SELECT * FROM Orders WHERE CustomerID = 123;

Regularly monitoring slow queries with Dynamic Management Views (DMVs) like sys.dm_exec_query_stats is a best practice.

25) What is Dynamic SQL and what are its advantages and risks?

Dynamic SQL allows you to construct SQL statements dynamically at runtime using variables.

Example:

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Employees WHERE Dept = ''' + @Dept + '''';
EXEC(@sql);

Advantages:

  • Flexibility for variable table names or filters.
  • Reusable for multiple schemas.

Disadvantages:

  • Vulnerable to SQL Injection if not parameterized.
  • Harder to debug and maintain.

Always use sp_executesql with parameters for safety.

26) What are temporary objects in T-SQL and how do they differ?

Temporary objects are stored in TempDB and help manage intermediate data.

Object Type Scope Example
Local Temp Table Session-specific CREATE TABLE #TempTable
Global Temp Table Visible to all sessions CREATE TABLE ##TempGlobal
Table Variable Batch-specific DECLARE @Temp TABLE (...)

Best Practices:

  • Prefer table variables for smaller datasets.
  • Use local temp tables for larger data with indexing needs.
  • Drop temp tables explicitly to release resources faster.

27) How do you use window partitioning functions in T-SQL?

Partitioning allows applying window functions to specific subsets of data.

Example:

SELECT
    Department,
    EmpName,
    Salary,
    RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankInDept
FROM Employees;

Benefits:

  • Efficiently calculates rankings, totals, and averages per group.
  • Eliminates need for self-joins or subqueries.

Use Cases: Salary bands, sales rankings, and trend analytics.

28) What is the difference between UNION and UNION ALL in T-SQL?

Clause Duplicates Performance Use Case
UNION Removes duplicates Slower (uses sort/distinct) Combining result sets cleanly
UNION ALL Keeps duplicates Faster Aggregation or data migration

Example:

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

Prefer UNION ALL when duplicates are acceptable and performance matters.

29) How do you work with JSON data in T-SQL?

SQL Server supports native JSON functions for parsing and generating JSON data.

Example:

DECLARE @json NVARCHAR(MAX) = '{"Name":"John","Age":30}';
SELECT JSON_VALUE(@json, '$.Name') AS Name;

Key Functions:

  • JSON_VALUE() → Extracts scalar values.
  • JSON_QUERY() → Extracts objects/arrays.
  • OPENJSON() → Parses JSON into rows.
  • FOR JSON → Converts query results into JSON format.

Useful for APIs, hybrid systems, and NoSQL integrations.

30) How can you manage and optimize TempDB in SQL Server?

TempDB is a system database critical for temporary storage and versioning. Mismanagement can cause severe performance issues.

Optimization Techniques:

  1. Place TempDB on fast storage (SSD).
  2. Pre-size data and log files.
  3. Use multiple data files (1 per CPU core up to 8).
  4. Monitor with sys.dm_db_file_space_usage.
  5. Regularly clean up temporary objects.

Example Query:

SELECT * FROM sys.dm_db_file_space_usage;

Managing TempDB proactively avoids contention on allocation pages and improves overall database throughput.

31) What are query hints in T-SQL, and when should they be used?

Query hints instruct the SQL Server optimizer to alter its normal execution plan.

They should be used sparingly — only when you fully understand the data distribution and execution context.

Example:

SELECT * FROM Orders WITH (NOLOCK) WHERE CustomerID = 102;

Common Hints:

  • NOLOCK: Reads without locks (may read uncommitted data).
  • FORCESEEK: Forces index seek instead of scan.
  • OPTIMIZE FOR: Guides parameter values for plan generation.
  • RECOMPILE: Forces recompilation for each execution.

Caution: Overusing hints can degrade performance as data grows or patterns change. Use them only when the optimizer’s plan is provably inefficient.

32) Explain the concept of execution plan caching in SQL Server.

SQL Server caches execution plans to avoid recompilation for recurring queries.

When the same query executes again with identical structure, it reuses the cached plan, improving performance.

Example:

EXEC GetCustomerOrders @CustomerID = 101;

Benefits:

  • Reduces CPU overhead.
  • Improves consistency in response time.

Problems:

  • Parameter sniffing can cause inefficient plans.
  • Plan cache bloat may consume memory.

Mitigation: Use OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN where parameters vary widely.

33) What is parameter sniffing, and how can it affect performance?

Parameter sniffing occurs when SQL Server uses parameter values from the first execution of a query to generate a plan that is then reused — even if it’s suboptimal for later executions.

Example Scenario:

  • First execution: small dataset → index seek plan.
  • Next execution: large dataset → same plan reused, but slow.

Solutions:

  1. Use OPTION (RECOMPILE) to generate fresh plans.
  2. Use local variables to mask parameter values.
  3. Use OPTIMIZE FOR or OPTIMIZE FOR UNKNOWN.

Parameter sniffing is one of the top real-world culprits of unpredictable performance in T-SQL.

34) How do you monitor and analyze query performance in SQL Server?

You can use several tools and DMVs to profile and tune performance:

  • Execution Plans: Ctrl + M in SSMS or sys.dm_exec_query_plan.
  • DMVs:

    • sys.dm_exec_query_stats – CPU & duration.
    • sys.dm_exec_sql_text – SQL text.
    • sys.dm_exec_requests – Active queries.
  • Performance Monitor & Extended Events for long-term tracking.

Example:

SELECT TOP 5
    total_worker_time / execution_count AS AvgCPU,
    total_elapsed_time / execution_count AS AvgTime,
    SUBSTRING(qt.text, 1, 100) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgTime DESC;

35) Explain the role of statistics in query optimization.

Statistics describe data distribution (e.g., distinct values, density, histogram) that the optimizer uses to estimate cardinality.

If statistics are outdated, SQL Server may choose poor plans.

Key Commands:

  • UPDATE STATISTICS Employees;
  • sp_updatestats;
  • Auto-update setting: enabled by default.

Best Practices:

  • Keep AUTO_UPDATE_STATISTICS enabled.
  • For large tables, schedule manual updates.
  • Use FULLSCAN for critical indexes.

Out-of-date statistics are a silent killer of performance.

36) What is the difference between an index seek and an index scan?

Operation Description Performance Use Case
Index Seek Navigates directly to matching rows Fast Highly selective queries
Index Scan Reads all index entries sequentially Slower Low-selectivity queries

Example:

SELECT * FROM Orders WHERE OrderID = 123; -- Seek
SELECT * FROM Orders WHERE Status = 'Active'; -- May Scan

Optimization Tip: Create filtered or covering indexes to turn scans into seeks.

37) Explain partitioned tables and their advantages.

Partitioning divides a large table into smaller, manageable pieces (partitions), often based on a range column (like date).

Benefits:

  • Faster data management (load/unload by partition).
  • Improved query performance on large datasets.
  • Parallel processing for partitioned scans.

Example:

CREATE PARTITION FUNCTION pfRange (DATETIME)
AS RANGE LEFT FOR VALUES ('2022-12-31', '2023-12-31');

Use Case: Data warehouses handling billions of rows where old partitions can be archived efficiently.

38) What are recursive CTEs, and what limitations do they have?

A recursive Common Table Expression (CTE) references itself, typically for hierarchical data like org charts or tree structures.

Example:

WITH EmployeeCTE AS (
    SELECT EmpID, ManagerID, EmpName FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmpID, e.ManagerID, e.EmpName
    FROM Employees e
    INNER JOIN EmployeeCTE c ON e.ManagerID = c.EmpID
)
SELECT * FROM EmployeeCTE;

Limitations:

  • Default recursion limit = 100 levels.
  • Can cause performance issues if recursion depth is high.
  • Use OPTION (MAXRECURSION n) to adjust limit.

39) How does SQL Server handle errors internally in transactions?

When an error occurs within a transaction:

  • If it’s severe (level > 20), the connection terminates immediately.
  • If non-severe, it can be caught with TRY...CATCH.

Example:

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE Accounts SET Balance -= 500 WHERE ID = 1;
    INSERT INTO AuditLog VALUES ('Debit');
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

Best Practice: Always wrap DML in TRY…CATCH for error resilience.

40) What are some advanced T-SQL performance tuning techniques?

  1. Avoid scalar UDFs in SELECT — inline functions are faster.
  2. Use filtered indexes to reduce index size.
  3. Leverage in-memory OLTP (Hekaton) for high-concurrency systems.
  4. Batch mode execution on columnstore indexes for analytics.
  5. Eliminate implicit conversions by matching data types.
  6. Use query store to compare historical plans.

Example to detect implicit conversions:

SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%';

Performance tuning is an ongoing process — not a one-time event.

41) How would you identify the most resource-intensive queries in SQL Server?

You can identify high-cost queries using Dynamic Management Views (DMVs) that record historical execution stats.

Example:

SELECT TOP 10
    total_logical_reads / execution_count AS AvgReads,
    total_worker_time / execution_count AS AvgCPU,
    total_elapsed_time / execution_count AS AvgDuration,
    SUBSTRING(qt.text, 1, 200) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgCPU DESC;

Key Metrics:

  • AvgCPU: Average CPU time per execution.
  • AvgReads: I/O intensity.
  • AvgDuration: Execution latency.

This approach helps DBAs isolate heavy queries before users even notice performance degradation.

42) How can you detect and fix missing indexes in SQL Server?

SQL Server automatically tracks missing index recommendations through DMVs.

Example:

SELECT
    migs.user_seeks AS Seeks,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.user_seeks DESC;

Best Practices:

  • Prioritize high-seek indexes first.
  • Verify via execution plans before creation.
  • Avoid over-indexing — it slows down writes.

43) What is the difference between database mirroring, replication, and log shipping?

Feature Purpose Real-time Failover Complexity
Mirroring High-availability database copy Yes Automatic Medium
Replication Data distribution across databases Partial Manual High
Log Shipping Backup-based DR strategy No Manual Low

Usage Guidance:

  • Mirroring → OLTP high-availability systems.
  • Replication → Distributed reporting.
  • Log Shipping → Disaster recovery setups.

44) How do you troubleshoot blocking issues in SQL Server?

Blocking occurs when one process holds locks that another process needs.

To identify blockers:

SELECT
    blocking_session_id AS Blocker,
    session_id AS Blocked,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Solutions:

  • Reduce transaction length.
  • Use snapshot isolation.
  • Tune queries to minimize locking.
  • Identify long-running open transactions with DBCC OPENTRAN.

45) How does SQL Server’s Query Store help in performance tuning?

Query Store captures query text, plans, and runtime stats — enabling plan regression analysis.

It helps identify when a query suddenly becomes slow due to plan changes.

Example:

SELECT q.query_id, p.plan_id, rs.avg_duration
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

Benefits:

  • Compare historical plans.
  • Force good plans.
  • Track performance trends over time.

46) How can you prevent SQL injection in T-SQL applications?

Primary Defenses:

  1. Use parameterized queries via sp_executesql.
  2. Validate and sanitize all user inputs.
  3. Avoid dynamic SQL concatenation.
  4. Employ least privilege principle for database accounts.

Secure Example:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM Employees WHERE Dept = @Dept';
EXEC sp_executesql @sql, N'@Dept NVARCHAR(50)', @Dept = 'HR';

Even though SQL injection is application-level, DBAs must audit stored procedures and logs for unparameterized execution.

47) How do you use Extended Events for deep performance monitoring?

Extended Events (XEvents) is a lightweight performance monitoring framework — a modern alternative to SQL Trace.

Example:

CREATE EVENT SESSION TrackQueries
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(WHERE duration > 1000)
ADD TARGET package0.event_file (SET filename = 'C:\Temp\QueryMonitor.xel');
ALTER EVENT SESSION TrackQueries ON SERVER STATE = START;

Use Cases:

  • Monitor high CPU queries.
  • Capture deadlocks or missing indexes.
  • Profile long-running statements in production with minimal overhead.

48) What are filtered indexes, and when should they be used?

A filtered index indexes only a subset of rows that satisfy a filter condition — improving performance and reducing storage.

Example:

CREATE INDEX IX_ActiveEmployees
ON Employees (Department)
WHERE Status = 'Active';

Benefits:

  • Smaller index size.
  • Faster maintenance.
  • Optimized for selective queries.

Best For: Columns with skewed data distribution (e.g., active vs inactive records).

49) How do you migrate data safely between SQL Server environments?

Safe data migration involves planning for consistency, downtime, and rollback.

Best Practices:

  1. Use transactional replication or change data capture (CDC) for live sync.
  2. Disable constraints and triggers temporarily.
  3. Use BCP or SSIS for bulk data transfer.
  4. Validate row counts and checksums.
  5. Always run post-migration integrity checks (DBCC CHECKDB).

Example:

bcp Database.dbo.Table out TableData.dat -n -S Server -T

Testing migration scripts in staging is non-negotiable.

50) How do you identify and fix parameter-sensitive query (PSQ) problems?

Parameter-sensitive queries perform inconsistently based on parameter values — a frequent real-world challenge.

Detection: Use Query Store or sys.dm_exec_query_stats to identify multiple plans for one query.

Fix Strategies:

  1. Use OPTION (RECOMPILE) for each execution.
  2. Use OPTIMIZE FOR UNKNOWN to create a generic plan.
  3. Create plan guides to enforce optimal execution paths.
  4. Use query hints only if necessary.

Parameter-sensitive issues require balancing between plan stability and performance predictability.

🔍 Top T-SQL Interview Questions with Real-World Scenarios & Strategic Responses

1) What is the difference between INNER JOIN and LEFT JOIN in T-SQL?

Expected from candidate: The interviewer wants to assess your understanding of join operations and how data relationships are managed in SQL queries.

Example answer: An INNER JOIN returns only the rows that have matching values in both tables, while a LEFT JOIN returns all rows from the left table, along with the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table. This distinction is crucial when working with partial or optional relationships in databases.

2) How would you identify and remove duplicate records from a table in T-SQL?

Expected from candidate: The interviewer wants to see your ability to use window functions and CTEs to handle data quality issues.

Example answer: I would use a Common Table Expression (CTE) combined with the ROW_NUMBER() function to identify duplicates. For example:

WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn
  FROM MyTable
)
DELETE FROM CTE WHERE rn > 1;

This approach helps remove duplicates while retaining one unique record for each group.

3) Can you explain what a CTE (Common Table Expression) is and when you would use it?

Expected from candidate: The interviewer is checking your knowledge of query structuring and temporary result sets.

Example answer: A CTE is a temporary result set defined within the execution scope of a single query. It is useful for simplifying complex joins and subqueries, improving readability, and making recursive queries possible. In my previous role, I used CTEs frequently to break down multi-step aggregation logic into more maintainable components.

4) How do you handle performance tuning in T-SQL queries?

Expected from candidate: The interviewer wants to evaluate your experience with query optimization and performance troubleshooting.

Example answer: I start by examining the execution plan to identify slow operations like table scans or expensive joins. I then check for missing indexes, redundant subqueries, or inefficient joins. I also analyze statistics and use indexing strategies, such as covering indexes or filtered indexes, to improve performance. Lastly, I review query logic to ensure it leverages set-based operations rather than row-by-row processing.

5) Describe a time when you had to debug a slow-running query in production. What steps did you take?

Expected from candidate: This behavioral question evaluates your real-world problem-solving and communication skills.

Example answer: At a previous position, a report query was taking over 20 minutes to execute. I analyzed the execution plan and discovered that one of the joins was missing an index on a foreign key column. After creating the index and updating statistics, the query runtime dropped to under 30 seconds. I also documented the fix and shared it with the team to prevent similar issues in the future.

6) What are temporary tables and table variables, and how do they differ?

Expected from candidate: The interviewer is testing your understanding of temporary data storage options in T-SQL.

Example answer: Temporary tables (#TempTable) are created in the tempdb database and support indexes, constraints, and statistics. Table variables (@TableVar) are stored in memory and have limited statistics support, making them suitable for smaller datasets. Temporary tables are better for large or complex datasets, while table variables are more efficient for small, short-lived data.

7) How would you handle error handling and transactions in T-SQL?

Expected from candidate: The interviewer is checking your understanding of transactional integrity and exception handling.

Example answer: I use BEGIN TRANSACTION, COMMIT, and ROLLBACK statements to ensure data consistency. I also include TRY...CATCH blocks to handle errors gracefully. For example:

BEGIN TRY
  BEGIN TRANSACTION
    -- SQL operations here
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  PRINT ERROR_MESSAGE()
END CATCH

This approach prevents partial data updates when errors occur.

8) How do you use window functions in T-SQL, and can you give an example?

Expected from candidate: The interviewer wants to assess your proficiency with advanced analytical queries.

Example answer: Window functions allow calculations across sets of rows related to the current row without collapsing data. For example:

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

This assigns ranking numbers to employees based on salary, making it easy to analyze performance trends.

9) Tell me about a complex T-SQL project you worked on and how you approached its challenges.

Expected from candidate: The interviewer is looking for depth in experience, problem-solving, and teamwork.

Example answer: In my last role, I built a data warehouse ETL pipeline using T-SQL stored procedures. The challenge was handling large data volumes efficiently. I optimized queries with partitioned tables, incremental loads, and batch processing. I also coordinated with the BI team to ensure consistent schema designs and improved reporting speed by over 40%.

10) How would you handle a situation where a stored procedure you wrote caused a deadlock in production?

Expected from candidate: The interviewer is testing your crisis management and technical awareness.

Example answer: I would first identify the deadlock using SQL Server’s sys.dm_tran_locks and deadlock graphs. Then, I would analyze the order of resource access and refactor the procedure to acquire locks in a consistent sequence. In my previous job, I also implemented retry logic for affected transactions and scheduled regular monitoring to detect similar patterns early.

Summarize this post with: