SQL Server DBA Interview Questions Part-2

SQL Server DBA Cheat-Sheet #3

(Advanced Concepts — Cursors, Fragmentation, Schema Design, Security, Partitioning & Query Optimization)

---

1 | Cursors in SQL Server

Concept	Summary (2025)
What is a Cursor?	A cursor lets you iterate row-by-row through a result set — like a loop in code.
Use case	Complex row-based logic that can’t be done in a set-based query (rare).
Caution	Cursors are slow and memory-intensive. Prefer set-based operations using JOIN, APPLY, CASE, MERGE, or window functions.

DECLARE my_cursor CURSOR FOR SELECT id FROM dbo.Customers;
OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @id;
-- Loop logic
CLOSE my_cursor;
DEALLOCATE my_cursor;

---

2 | Index Fragmentation Explained

Type	Cause	Fix
Internal	Excess free space inside pages (due to deletes/updates)	ALTER INDEX … REORGANIZE
External	Pages physically out of order	ALTER INDEX … REBUILD (or use Maintenance Plans / Ola scripts)

Run this to diagnose:

SELECT * FROM sys.dm_db_index_physical_stats
  (DB_ID(), OBJECT_ID('YourTable'), NULL, NULL, 'LIMITED');

---

3 | Schemas in SQL Server

Think of a schema as a “folder” inside your database for organizing objects.

• Syntax: CREATE SCHEMA Sales AUTHORIZATION dbo;

• Tables: Sales.Orders, Sales.Customers

• Benefits:

  • Security: Grant permissions at the schema level.

  • Organization: Group logically related objects.

  • Namespace separation: Avoid name collisions (e.g., HR.Employees vs. Sales.Employees).

---

4 | DELETE vs TRUNCATE

DELETE	TRUNCATE
Row-by-row operation (fully logged)	Minimal logging (faster)
Can use WHERE	Cannot filter; deletes all rows
Triggers fire	Triggers don’t fire
Retains identity seed	Resets identity seed (in most versions)

---

5 | Parameterized Queries = Security

Parameterized SQL uses placeholders, reducing risk of SQL injection:

-- BAD (injectable)
EXEC('SELECT * FROM Users WHERE name = ''' + @name + '''')

-- GOOD (parameterized)
SELECT * FROM Users WHERE name = @name

All modern client libraries (ADO.NET, Entity Framework, etc.) support parameters.

---

6 | #Temporary Table vs Table Variable

Feature	#Temp Table	@Table Variable
Scope	Session-level	Batch/procedure-level
Supports indexes?	✅ (can CREATE or declare with index)	✅ (but no stats for optimizer)
Transaction control	✅	❌ (ignored by transactions)
Performance	Better for large sets / joins	Better for small datasets / simple procs

---

7 | HAVING Clause

Use HAVING to filter after aggregation:

-- Show products with more than 5 orders
SELECT ProductID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY ProductID
HAVING COUNT(*) > 5;

Use WHERE before grouping, HAVING after.

---

8 | Authentication Modes

Mode	Summary
Windows Authentication	Integrated login via Active Directory; best for security.
SQL Server Authentication	Username + password stored in SQL Server; less secure; used for app logins or cross-domain scenarios.

Enable both (Mixed Mode) if needed, but use Windows Auth whenever possible.

---

9 | SQL Server Agent

Automation powerhouse inside SQL Server.

• Runs Jobs: backups, ETL tasks, index rebuilds, alerts

• Monitors: job history, success/failure

• UI: Available in SSMS → “SQL Server Agent” → Jobs

Jobs include:

• Steps (T-SQL, SSIS packages, OS commands)

• Schedules

• Alerts and notifications (e.g., send email on job failure)

---

10 | Partitioning

Partitioning breaks a table/index into logical chunks (e.g., by date or region)
Benefits:

• Faster scans & maintenance

• Can switch partitions in/out (fast archiving)

-- Example (Partition by year)
CREATE PARTITION FUNCTION pfYearRange (int)
AS RANGE LEFT FOR VALUES (2019, 2020, 2021);

Use Partition Schemes + check execution plan for partition elimination.

---

11 | CLR Integration

CLR = Common Language Runtime (runs .NET code inside SQL Server)

• Build in C#, compile to DLL, and register:

CREATE ASSEMBLY MyAssembly FROM 'C:\my.dll'
WITH PERMISSION_SET = SAFE;

Use for:

• Regex

• File access

• Crypto

• Advanced math

Must enable CLR: sp_configure 'clr enabled', 1;

---

12 | Data Warehousing Overview

	Operational DB	Data Warehouse
Purpose	CRUD / transactions	Analysis, reporting
Design	OLTP (3NF)	OLAP (star/snowflake)
Data latency	Real-time	Batch-processed
Examples	Point-of-sale system	Sales dashboard, Power BI reports

ETL via SSIS or Azure Data Factory → Warehouse → Reports

---

13 | JOIN vs SUBQUERY

	JOIN	SUBQUERY
Combines tables in one flat result	Query inside a query (nested)
More readable & efficient for multi-table logic	Better for filtering or scalar values
Example:

-- JOIN
SELECT o.OrderID, c.Name
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

-- SUBQUERY
SELECT OrderID
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Region = 'East');

---

Final Tips for Students

1. Avoid cursors unless absolutely needed.

2. Indexes = performance, but watch for fragmentation.

3. Use parameterized queries — always.

4. Understand JOINs, temp tables, and execution plans deeply.

5. Practice daily in SSMS or Azure Data Studio.

✔ Mastering this cheat-sheet = solid SQL Server foundations.