🔹 SQL Server Interview Questions with Keywords, Functions & Transactions
1. Main SQL Server Keywords
These are frequently asked in interviews:
DDL (Data Definition Language):
CREATE,ALTER,DROP,TRUNCATE,RENAMEDML (Data Manipulation Language):
SELECT,INSERT,UPDATE,DELETE,MERGEDCL (Data Control Language):
GRANT,REVOKE,DENYTCL (Transaction Control Language):
BEGIN TRAN,COMMIT,ROLLBACK,SAVEPOINTOther Important Keywords:
WHERE,GROUP BY,HAVING,ORDER BY,DISTINCT,TOP,JOIN(INNER, LEFT, RIGHT, FULL),UNION,EXCEPT,INTERSECT,WITH(CTE),OVER,PARTITION BY
👉 Interview Tip: Be ready to explain differences, e.g., DELETE vs TRUNCATE vs DROP.
2. SQL Server Functions
SQL Server provides different types of functions. Common interview focus:
a) Aggregate Functions
COUNT(),SUM(),AVG(),MIN(),MAX()
b) String Functions
LEN(),SUBSTRING(),CHARINDEX(),REPLACE(),UPPER(),LOWER(),LTRIM(),RTRIM()
c) Date & Time Functions
GETDATE(),SYSDATETIME(),DATEADD(),DATEDIFF(),DATENAME(),CONVERT()
d) Mathematical Functions
ABS(),ROUND(),CEILING(),FLOOR(),POWER()
e) Ranking & Window Functions
ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE(),LEAD(),LAG(),OVER(PARTITION BY...)
f) System Functions
ISNULL(),COALESCE(),CAST(),CONVERT(),NULLIF()
👉 Interview Tip: Be ready to write queries using ROW_NUMBER() or LAG() for solving ranking or difference problems.
3. Transactions in SQL Server
A transaction is a sequence of operations performed as a single logical unit of work.
Key Components
BEGIN TRANSACTION → Start a transaction
COMMIT → Save all changes
ROLLBACK → Undo all changes
SAVEPOINT → Rollback partially to a specific point
@@TRANCOUNT → Check active transaction count
Transaction Properties (ACID)
Atomicity – All or nothing
Consistency – Data remains valid
Isolation – Transactions execute independently
Durability – Committed data is permanent
Transaction Isolation Levels
READ UNCOMMITTED– Dirty reads allowedREAD COMMITTED– Prevents dirty reads (default in SQL Server)REPEATABLE READ– Prevents dirty & non-repeatable readsSNAPSHOT– Provides version-based readsSERIALIZABLE– Highest isolation, prevents all anomalies
👉 Interview Tip: Expect a question like
"If two users update the same row at the same time, what happens?"
Answer involves locking, blocking, and isolation levels.
4. Sample Interview Questions
Difference between DELETE, TRUNCATE, DROP?
What is a CTE (Common Table Expression) and where do you use it?
Explain Clustered vs Non-Clustered Index.
What are Aggregate Functions? Give examples.
Explain ROW_NUMBER() vs RANK() vs DENSE_RANK().
What are ACID properties of a transaction?
Explain Deadlock and how to handle it.
What are isolation levels in SQL Server?
Explain savepoint in a transaction.
How do you optimize queries in SQL Server?
✅ With this, you’re ready for keywords, functions, and transaction-based questions in SQL Server interviews.
🔹 SQL Server Interview Questions & Answers
1. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
DELETE: Removes rows one by one, can haveWHEREclause, logs each row. Rollback possible.TRUNCATE: Removes all rows, noWHERE, faster, minimal logging, resets identity.DROP: Deletes entire table structure + data. Cannot rollback once executed.
2. What are SQL Server functions and their types?
Answer:
Functions in SQL Server are built-in methods used to perform operations on data.
Aggregate:
COUNT(),SUM(),AVG()String:
LEN(),SUBSTRING(),REPLACE()Date & Time:
GETDATE(),DATEDIFF(),DATEADD()Math:
ROUND(),ABS(),CEILING()Ranking/Window:
ROW_NUMBER(),RANK(),DENSE_RANK(),LAG()System:
ISNULL(),COALESCE(),CAST(),CONVERT()
3. What are ACID properties in SQL Server transactions?
Answer:
Atomicity – Entire transaction succeeds or fails.
Consistency – Ensures data integrity after transaction.
Isolation – Transactions do not affect each other.
Durability – Committed data is permanent even after a crash.
4. What are the different types of joins in SQL Server?
Answer:
INNER JOIN – Returns only matching rows.
LEFT JOIN – All from left + matched from right.
RIGHT JOIN – All from right + matched from left.
FULL OUTER JOIN – All rows from both tables.
CROSS JOIN – Cartesian product of two tables.
5. What are transaction control commands in SQL Server?
Answer:
BEGIN TRAN→ Starts a transaction.COMMIT→ Saves changes permanently.ROLLBACK→ Reverts changes.SAVEPOINT→ Marks a point to rollback partially.@@TRANCOUNT→ Returns active transaction count.
6. What are isolation levels in SQL Server?
Answer:
Read Uncommitted → Dirty reads allowed.
Read Committed → No dirty reads (Default).
Repeatable Read → Prevents dirty + non-repeatable reads.
Serializable → Prevents all anomalies, strictest.
Snapshot → Provides version-based consistent reads.
7. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
Answer:
ROW_NUMBER()→ Unique sequential numbers (no duplicates).RANK()→ Skips numbers if there are ties.DENSE_RANK()→ No gaps in ranking, even with ties.
8. What is a CTE (Common Table Expression)?
Answer:
A temporary result set defined with WITH keyword, used for recursive queries and improving readability.
Example:
WITH EmpCTE AS (
SELECT EmpId, ManagerId, Name
FROM Employee
)
SELECT * FROM EmpCTE;
9. What is the difference between ISNULL() and COALESCE()?
Answer:
ISNULL(expr, replacement)→ Replaces NULL with replacement, only 2 parameters.COALESCE(expr1, expr2, expr3, …)→ Returns first non-NULL value, supports multiple parameters.
10. What is a deadlock and how do you prevent it?
Answer:
Deadlock occurs when two or more transactions wait for each other’s locks, causing infinite blocking.
Prevention methods:
Access tables in same order.
Keep transactions short.
Use proper indexing.
Use
SET DEADLOCK_PRIORITYto control victim selection.
11. What are indexes and their types in SQL Server?
Answer:
Clustered Index: Stores data physically sorted (only one per table).
Non-Clustered Index: Separate structure with pointer to data (multiple allowed).
Other types: Unique Index, Filtered Index, Full-text Index, Columnstore Index.
12. What are Savepoints in a transaction?
Answer:
Savepoints allow rollback to a specific point within a transaction.
Example:
BEGIN TRAN;
INSERT INTO Orders VALUES (1, 'Test1');
SAVE TRAN SavePoint1;
INSERT INTO Orders VALUES (2, 'Test2');
ROLLBACK TRAN SavePoint1; -- Rolls back only second insert
COMMIT;
✅ This covers most common SQL Server interview questions with answers around keywords, functions, and transactions.
