Junior MS SQL Interview Questions

Clustered Index: Defines the physical order of rows in the table. Implemented as a B-Tree with root, intermediate, and leaf nodes. The leaf level contains actual data rows.

Non-Clustered Index: Also a B-Tree, but leaf nodes store index keys and row locators (RID or clustered key).

Performance Impact: Clustered indexes help range queries, while non-clustered indexes help quick lookups. Poor clustered key choice can make indexes large and slow.

Index Seek: SQL jumps directly to matched rows. Happens with selective filters and proper indexes.

Index Scan: SQL reads entire index. Happens with broad filters or missing indexes.

Table Scan: Reads whole table. Used when no index exists or table is small.

SQL Server uses a cost-based optimizer that parses the query, evaluates different plan options, estimates CPU/I/O cost, and chooses the lowest-cost plan.

Statistics contain value distribution info. SQL uses them to estimate row counts. Bad or outdated statistics cause wrong estimations, leading to slow plans.

A covering index contains all columns needed for a query. It removes key lookups and improves performance in read-heavy workloads.

Key lookup happens when a non-clustered index finds a row but SQL needs extra columns. It causes many random I/O operations and slows queries.

When too many row/page locks exist, SQL upgrades to a table lock. This reduces overhead but lowers concurrency.

Page splitting happens when SQL inserts rows into a full page. It increases fragmentation. Fill Factor leaves free space to reduce page splits.

Buffer Pool: Stores data and index pages.

Plan Cache: Stores compiled query plans for reuse.

SQL optimizes a plan based on first-used parameter value. Fix options include using RECOMPILE, OPTIMIZE FOR UNKNOWN, or rewriting queries.

Use when queries target specific subsets (e.g., Active records). Improves performance and reduces index size.

A heap is a table without clustered index. Good for bulk loads but slow for lookups. Most tables should have a clustered index.

Partitioning splits a table into smaller pieces, improving maintenance and performance for large datasets.

Query Store keeps execution history, performance metrics, and helps detect regressions. It allows forcing stable plans.

It predicts row counts for query operations. Accurate estimates help SQL choose the best join and operator strategy.

SQL requests memory for sorting and hashing. Over-grants cause waits; under-grants cause spills to TempDB.

Hotspots occur when many inserts hit the same index page (e.g., identity keys). They cause blocking and contention.

Pessimistic uses locks to avoid conflicts. Optimistic uses row versioning to detect conflicts at commit without blocking readers.

Wait types show where SQL is spending time (CPU, I/O, locks). Examples: CXPACKET, PAGEIOLATCH, LCK_M_X.

TempDB stores temp tables, spills, versioning, and intermediate results. Best practices include multiple equal-sized data files and SSD storage.