Transact-SQL (T-SQL) is Microsoft’s proprietary extension to SQL.
It plays a crucial role in database development, administration, and analysis. Whether you are preparing for a Database Developer, SQL Server Administrator, or Data Analyst role, these T-SQL interview questions will help you succeed.
Let’s dive right in!
📋 Basic T-SQL Interview Questions:
1. What is T-SQL?
Answer:
T-SQL stands for Transact-SQL. It extends SQL by adding procedural programming, local variables, error handling, and more advanced transaction control.
2. How is T-SQL different from standard SQL?
Answer:
T-SQL includes procedural programming features like loops, conditions, variables, and error handling that standard SQL does not have.
3. What are common data types used in T-SQL?
Answer:
- INT, BIGINT
- FLOAT, DECIMAL
- CHAR, VARCHAR, NVARCHAR
- DATETIME, DATE
- BIT, MONEY
4. What is a stored procedure in T-SQL?
Answer:
A stored procedure is a group of T-SQL statements that can be executed repeatedly. It helps improve performance and security.
5. What are Triggers?
Answer:
Triggers are special types of stored procedures that automatically execute when certain events occur in a table (INSERT, UPDATE, DELETE).
6. What is a CTE (Common Table Expression)?
Answer:
A CTE provides a way to create a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
7. Write a simple CTE example.
Answer:
WITH EmployeeCTE AS (
SELECT EmployeeID, Name
FROM Employees
)
SELECT * FROM EmployeeCTE;
🛠️ Core T-SQL Interview Questions:
8. What is the difference between WHERE and HAVING clauses?
Answer:
- WHERE filters rows before grouping.
- HAVING filters groups after aggregation.
9. What are the types of JOINs in T-SQL?
Answer:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- CROSS JOIN
10. Explain the difference between UNION and UNION ALL.
Answer:
- UNION removes duplicate records.
- UNION ALL includes duplicates.
11. What is a Temp Table in T-SQL?
Answer:
Temporary tables are stored in tempdb and are used for storing intermediate results temporarily.
12. What is the difference between a Local Temp Table and a Global Temp Table?
Answer:
- Local Temp Table (
#tableName) exists for the duration of a session. - Global Temp Table (
##tableName) is available to all sessions.
13. How do you handle errors in T-SQL?
Answer:
Using TRY...CATCH blocks to catch and handle errors.
Example:
BEGIN TRY
-- Code
END TRY
BEGIN CATCH
-- Error Handling Code
END CATCH
14. What is a Table Variable?
Answer:
A table variable is a special type of variable that holds data in a table format and is stored in memory.
15. Difference between DELETE and TRUNCATE?
Answer:
- DELETE can have WHERE clause and logs each row deletion.
- TRUNCATE removes all rows quickly but cannot be used with WHERE.
🚀 Advanced T-SQL Interview Questions:
16. What are window functions in T-SQL?
Answer:
Functions like ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() that perform calculations across sets of rows related to the current row.
17. Example of using ROW_NUMBER().
Answer:
SELECT Name, ROW_NUMBER() OVER (ORDER BY Salary DESC) as RowNum
FROM Employees;
18. What is the difference between RANK() and DENSE_RANK()?
Answer:
- RANK() leaves gaps in ranking when there are ties.
- DENSE_RANK() does not leave gaps.
19. What is Dynamic SQL?
Answer:
SQL statements created and executed at runtime.
Example:
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM Employees'
EXEC(@sql)
20. What is a cursor in T-SQL?
Answer:
A cursor is a database object that allows row-by-row processing of query results.
21. Types of Cursors in T-SQL?
Answer:
- Static
- Dynamic
- Forward-only
- Keyset-driven
22. What is Isolation Level in T-SQL Transactions?
Answer:
It determines how transaction integrity is visible to other transactions.
Types:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
- SNAPSHOT
23. How to improve T-SQL Query Performance?
Answer:
- Use indexes
- Avoid cursors
- Optimize joins
- Minimize subqueries
- Use proper indexing strategies
24. What is the use of NOLOCK hint?
Answer:
It allows reading data without acquiring shared locks, thus improving query performance but with risk of dirty reads.
25. What are User-Defined Functions (UDFs)?
Answer:
Functions created by users to encapsulate reusable logic.
🧠 Scenario-Based T-SQL Interview Questions:
26. How do you retrieve the second highest salary in a table?
Answer:
SELECT MAX(Salary) FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
27. How would you find duplicate records?
Answer:
SELECT Name, COUNT(*)
FROM Employees
GROUP BY Name
HAVING COUNT(*) > 1;
28. How can you update data from one table to another?
Answer:
UPDATE A
SET A.Name = B.Name
FROM TableA A
INNER JOIN TableB B ON A.ID = B.ID;
29. What is Pivoting and Unpivoting in T-SQL?
Answer:
Pivoting turns rows into columns, Unpivoting turns columns into rows.
30. How would you create an index on a table?
Answer:
CREATE INDEX idx_name ON Employees (Name);
📢 Conclusion:
Preparing well for these T-SQL interview questions will give you a huge advantage over others.
Understanding query optimization, indexing, error handling, and real-world scenarios will help you stand out in technical interviews.
Good luck with your preparation and your next job interview!