Sql Server Imp Queries and FAQ's

🔹 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, RENAME

• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE, MERGE

• DCL (Data Control Language): GRANT, REVOKE, DENY

• TCL (Transaction Control Language): BEGIN TRAN, COMMIT, ROLLBACK, SAVEPOINT

• Other 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 allowed

• READ COMMITTED – Prevents dirty reads (default in SQL Server)

• REPEATABLE READ – Prevents dirty & non-repeatable reads

• SNAPSHOT – Provides version-based reads

• SERIALIZABLE – 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

1. Difference between DELETE, TRUNCATE, DROP?

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

3. Explain Clustered vs Non-Clustered Index.

4. What are Aggregate Functions? Give examples.

5. Explain ROW_NUMBER() vs RANK() vs DENSE_RANK().

6. What are ACID properties of a transaction?

7. Explain Deadlock and how to handle it.

8. What are isolation levels in SQL Server?

9. Explain savepoint in a transaction.

10. 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 have WHERE clause, logs each row. Rollback possible.

• TRUNCATE: Removes all rows, no WHERE, 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_PRIORITY to 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.