Sep 2, 2026

SQL

by on

Technical Interview Guide: Welcome to this comprehensive, in-depth preparation guide featuring curated, real-world, and scenario-based SQL and Microsoft SQL Server technical interview questions and answers. Designed specifically for intermediate to advanced database developers, DBAs, and software architects, this guide covers foundational concepts, internal framework architecture, performance optimization, system design principles, and production troubleshooting to help you clear technical rounds with confidence.

SQL & SQL Server Guide

Topics covered: SQL Server Architecture, Query Compilation & Plan Caching, Parameter Sniffing, Join Algorithms (Nested Loops, Hash, Merge), B-Tree Clustered/Non-Clustered Indexes, Statistics & Cardinality Estimation, Pessimistic vs Optimistic Isolation (RCSI/Snapshot), Deadlock Resolution, Write-Ahead Logging & Checkpoints, Page Architecture (PFS/GAM/SGAM), TempDB Metadata Contention, Window Framing, Table Partitioning, Wait Stats (CXPACKET, LCK_M, PAGEIOLATCH), and Always On Availability Groups.

Query Processing Lifecycle & Relational Engine Internals

Answer:
The query lifecycle passes through two major sub-systems: the Relational Engine and the Storage Engine:
  1. SNI (SQL Server Network Interface): Receives client TDS (Tabular Data Stream) packets over TCP/IP, Named Pipes, or Shared Memory.
  2. Parser: Validates T-SQL syntax and emits an initial Parse Tree (Logical Tree).
  3. Binder / Algebrizer: Resolves table, column, and data type references against internal metadata catalogs, verifies column permissions, performs implicit type conversions, and emits a validated Query Processor Tree.
  4. Cost-Based Optimizer (CBO): Generates equivalent candidate execution trees, analyzes search arguments (SARGs), consults distribution statistics, evaluates available indexes, and outputs an optimized physical Execution Plan.
  5. Execution Engine & Storage Engine: Coordinates memory grants via the Workspace Memory Broker, accesses data pages via the Buffer Pool, requests locking/latching mechanisms, evaluates access paths, and returns rowsets back to the client application.
Interview Tip: Emphasize that the query optimizer does not attempt to calculate the mathematically perfect plan; it searches for the cheapest plan found within an acceptable optimization timeframe before compilation cost outweighs execution time.
Answer:
T-SQL statements are written syntactically starting with SELECT, but the SQL Relational Engine evaluates query clauses in a strict logical sequence:
  1. FROM (including JOIN evaluations, APPLY, and table-valued functions)
  2. ON (join filter conditions)
  3. WHERE (filters rows prior to grouping and aggregation)
  4. GROUP BY (partitions rows into aggregated buckets)
  5. WITH CUBE / WITH ROLLUP / GROUPING SETS
  6. HAVING (filters aggregated groups)
  7. SELECT (evaluates scalar expressions, projections, and assigns column aliases)
  8. DISTINCT (deduplicates projection rows)
  9. ORDER BY (imposes a deterministic presentation sequence)
  10. TOP / OFFSET...FETCH (limits rows emitted to the consumer)
Because WHERE runs at step 3 while SELECT aliases are assigned at step 7, column aliases defined in SELECT cannot be evaluated directly inside WHERE or HAVING clauses.
Answer:
A predicate is SARGable (Search Argument Able) when the Query Optimizer can isolate an index key column cleanly on one side of a comparison operator, enabling an efficient Index Seek operation directly along the B-Tree index path.

Common Non-SARGable Patterns & Fixes:
  • Scalar Functions on Index Columns: 
    -- Non-SARGable (Forces Index Scan):
WHERE DATEDIFF(day, CreatedDate, GETDATE()) <= 30;

-- SARGable (Allows Index Seek):
WHERE CreatedDate >= DATEADD(day, -30, CAST(GETDATE() AS DATE));
  • Mathematical Operations on Columns: WHERE Salary * 1.10 > 50000 forces a full table scan. Rewrite as WHERE Salary > 50000 / 1.10.
  • Leading Wildcards: WHERE CustomerCode LIKE '%ABC' prevents index seek evaluation because B-Trees are sorted left-to-right.
  • Implicit Data Type Precedence: Comparing an NVARCHAR(50) parameter against a VARCHAR(50) indexed column forces the engine to wrap the table column in a hidden CONVERT_IMPLICIT() function, discarding index seeks.
Answer:
The SQL Server Optimizer uses a multi-stage cost heuristic (often referred to as the Simplification and Search Phases) to avoid excessive compilation overhead:
  • Phase 0 (Trivial Plan): Checks if there is only one viable physical plan (e.g., an unqualified SELECT * FROM Table WHERE PrimaryKey = @id). If found, optimization ends immediately with minimal CPU cost.
  • Simplification: Removes redundant joins (foreign key join elimination), evaluates constant expressions, and simplifies contradictory constraints (e.g., WHERE 1 = 0).
  • Phase 1 (Transaction Processing / Quick Plan): Explores basic nested loops, index seeks, and straightforward join permutations typical for OLTP workloads. Optimization halts if the plan cost falls below a low internal threshold.
  • Phase 2 (Full Optimization): Divided into sub-stages:
    • Stage 1: Evaluates more join types (Hash/Merge joins), parallel plans (if estimated cost exceeds the cost threshold for parallelism), and index combinations.
    • Stage 2: Deep optimization exploring complex transformations, index views, and partitioned access paths.
Optimization terminates if a low-enough cost plan is discovered or if an internal timeout/cost-threshold limit is reached (flagged as TimeOut in execution plan metadata).
Answer:
* Estimated Execution Plan: Generated strictly by the Relational Engine optimizer using metadata catalogs and statistics histograms without actually running the T-SQL commands. Does not reflect runtime metrics (such as memory spills, actual row counts, or thread wait times).
* Actual Execution Plan: Produced after query execution finishes. Contains both the optimizer estimates and the actual runtime metrics: elapsed CPU time, actual rows produced, execution iterations, actual memory grants, and tempdb spills.
* Live Query Statistics: Uses lightweight profiling infrastructure (sys.dm_exec_query_profiles) to stream real-time progress indicators, showing row traversal percentages across physical iterator operators while long-running queries are still executing.
Answer:
Standard procedural languages guarantee left-to-right short-circuit evaluation in boolean expressions (e.g., if (A && B) skips B if A is false).

In T-SQL, short-circuiting is NOT guaranteed. SQL is declarative; the Query Optimizer can freely reorder the evaluation sequence of WHERE predicates based on operator cost estimations: 
-- Might throw "Conversion failed when converting the varchar value 'abc' to data type int."
SELECT Col1 
FROM RawData 
WHERE ISNUMERIC(Col1) = 1 AND CAST(Col1 AS INT) > 100;
The optimizer may evaluate CAST(Col1 AS INT) > 100 before evaluating ISNUMERIC(Col1) = 1 if it believes the casting filter eliminates more rows faster.
Interview Tip: To enforce strict mathematical evaluation order in predicates, use a CASE expression or a computed column with explicit TRY_CAST() / TRY_CONVERT() guards.
Answer:
During the simplification phase, the optimizer detects logical contradictions between query predicates, check constraints, or foreign keys.

When a contradiction is verified, the engine does not access any data files or tables on disk; it injects a Constant Scan operator returning 0 rows: 
-- Query contradiction:
SELECT OrderId, Amount FROM Orders WHERE OrderId = 10 AND OrderId = 20;

-- Constraint contradiction:
-- If table has CHECK (Amount > 0):
SELECT OrderId, Amount FROM Orders WHERE Amount < -500;
Both queries compile to a plan with 0 physical reads because the optimizer proves mathematically that zero rows can satisfy the expression.
Answer:
* Non-Correlated Subqueries: Independent queries executed once. The optimizer computes the result set beforehand, caches it in memory or spools, and substitutes the scalar value or set into the outer query.
* Correlated Subqueries: Semantically depend on column values from the outer query row-by-row.

Internally, the Relational Engine flattens (decorrelates) correlated subqueries whenever possible into physical relational operators such as Semi-Joins (for EXISTS / IN) or Anti-Semi-Joins (for NOT EXISTS / NOT IN), allowing them to leverage standard Hash, Merge, or Nested Loops join mechanics rather than iterative, looping execution.
Answer:
If a query performs an INNER JOIN to a referenced parent table solely to evaluate foreign key columns, but selects no columns from that parent table: 
SELECT o.OrderId, o.OrderDate, o.CustomerId
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId;
If the foreign key constraint is trusted (not disabled or created with WITH NOCHECK) and the foreign key column is defined as non-nullable, the optimizer proves that every CustomerId in Orders guaranteed-matches a valid row in Customers. It completely eliminates the physical read/join to the Customers table from the execution plan.
Answer:
SQL Server's row-mode execution engine implements the classic Volcano Iterator Model. Every physical operator in an execution plan (Index Seek, Filter, Nested Loops, Hash Match) is encapsulated as an iterator class implementing three primary methods:
  • Open(): Initializes internal states, structures, memory grants, or child iterators.
  • GetNext(): Requests and pulls a single row from its downstream child operator.
  • Close(): Cleans up internal structures and resources.
Processing is demand-driven and streams backwards from the root node (leftmost operator) pulling rows one-by-one from the leaf operators (rightmost operators), which keeps memory footprints minimal during continuous streaming operations.
Answer:
* Row Mode: Iterators process data one single row at a time via the Volcano iterator loop. For queries processing millions of records, CPU instruction overhead, instruction cache misses, and function calls explode.
* Batch Mode: Introduced with Columnstore indexes and extended to standard rowstore heaps/indexes via Batch Mode on Rowstore (SQL Server 2019+). Iterators process chunks of rows (typically up to 900 rows per batch) packaged inside continuous memory vectors.

Batch mode utilizes modern CPU SIMD vector instructions, keeping tight loops inside the L1/L2 hardware CPU caches, often speeding up aggregate and hash join operations by 4x–10x.
Answer:
SQL implements three-valued logic: TRUE, FALSE, and UNKNOWN.

When ANSI_NULLS is set to ON (standard compliance):
  • Any equality or inequality check against NULL (e.g., Col = NULL or Col <> NULL) returns UNKNOWN.
  • WHERE clauses discard rows where the predicate evaluates to UNKNOWN or FALSE (only passing rows where the predicate evaluates strictly to TRUE).
  • To match nullability across indexes, the optimizer translates IS NULL into a targeted equality seek against the lowest boundary value of the index B-Tree, because SQL Server groups all NULL entries together at the lowest end of an index ordering.
Answer:
Prior to Adaptive Joins, the optimizer selected either a Nested Loops Join or a Hash Join at compile time based strictly on cardinality estimates. If estimates were wrong, a sub-optimal plan caused severe performance degradation.

Adaptive Joins:
  1. The compiler injects an Adaptive Join operator that contains both a Hash Join and a Nested Loops join sub-plan, alongside an internal Row Count Threshold.
  2. At runtime, the engine scans the build input.
  3. If the actual row count is below the threshold, it dynamically executes the Nested Loops Join.
  4. If the row count exceeds the threshold, it dynamically executes the Hash Join, avoiding memory bottlenecks or nested loops looping thrash.
Answer:
* Memory Grant Feedback: If a query is granted excessive memory that goes unused (starving concurrency) or is granted insufficient memory resulting in spills to TempDB, this IQP feature identifies the gap and adjusts the granted workspace memory downwards or upwards for subsequent executions directly inside the cached plan.
* CE (Cardinality Estimation) Feedback: Analyzes execution plan regressions caused by inaccurate assumption modeling (e.g., independence vs correlation of multi-column predicates). It persists learned feedback in Query Store to apply corrective query hints (e.g., ASSUME_MIN_SELECTIVITY_FOR_FILTER_INTERSECTION) automatically.
Answer:
* UNION ALL: Combines two or more record sets directly via a lightweight Concatenation operator. It preserves all duplicate rows, requires zero memory workspace grants, and does not require sorting or hashing.
* UNION: Implies strict set deduplication. The engine must concatenate inputs and inject an expensive blocking physical operator—typically a Sort (Distinct Sort) or an Aggregate (Hash Aggregate)—to identify and eliminate duplicates across all columns.

This requires an upfront memory grant, introduces potential TempDB spill risks, and blocks row streaming until all inputs are completely consumed.
© 2026 HelpBox.in :: All Rights Reserved
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)