Senior MS SQL Interview Questions

Stored procedures are precompiled program units that live inside SQL Server. Instead of sending raw SQL text for every request, the application sends only the procedure name and parameters. SQL Server then executes pre-validated logic using a cached execution plan.

Key advantages over raw ad-hoc SQL:

• Reduced network traffic: Only parameter values are sent, not large query strings.
• Plan reuse: SQL Server can cache and reuse execution plans for procedures, reducing compilation overhead and stabilizing performance.
• Centralized business logic: Data-related rules (validation, transformations, audit operations) are centralized and versioned at the database level, simplifying deployments and multi-application integration.
• Security and least-privilege: Applications can be granted EXECUTE rights on procedures instead of direct table access, improving security boundaries and auditability.
• Reduced SQL injection risk: The SQL logic is fixed inside the procedure; parameters are bound, greatly decreasing injection surfaces compared to concatenated SQL strings.

Stored procedures and functions both encapsulate reusable logic, but they serve different purposes and behave differently inside SQL Server.

Stored procedures:

• Primarily designed to perform actions: DML operations, transaction control, administrative tasks.
• Can return multiple result sets and output parameters.
• Cannot be directly used in SELECT, WHERE, or JOIN clauses.
• Commonly used as API endpoints from the application layer.

Functions:

• Intended for computation and value-returning logic.
• Scalar functions return a single value; table-valued functions return a rowset.
• Can be used inside SELECT, WHERE, and JOIN clauses like expressions or tables.
• Have tighter restrictions: no explicit transaction control; most cannot perform data-modifying side effects.

In short: procedures orchestrate operations, while functions compute values that integrate with queries.

SQL Server supports several function types, each with distinct performance and usage characteristics.

Scalar functions:

• Return a single scalar value (e.g., INT, VARCHAR).
• Evaluated per row when used in queries, often resulting in RBAR (Row-By-Agonizing-Row) execution.
• Act as black boxes to the optimizer, frequently preventing parallelism and leading to severe performance issues.

Inline table-valued functions (iTVFs):

• Return a table defined by a single RETURN (SELECT ...) statement.
• Logically similar to parameterized views.
• Fully inlined into the calling query, enabling the optimizer to generate efficient set-based plans with good cardinality estimates.
• Usually the best-performing function type for set-based logic.

Multi-statement table-valued functions (mTVFs):

• Declare an internal table variable and populate it across multiple statements.
• By default, SQL Server assumes a fixed row estimate (historically 1 row), often leading to poor plans.
• Limited statistics and cardinality information, frequently causing spills and suboptimal joins.

For performance-sensitive code, prefer inline TVFs over scalar or multi-statement TVFs whenever possible.

Scalar functions often look clean and reusable but can be hidden performance killers in production workloads.

Reasons they degrade performance:

• They are executed once per row in a result set, turning set-based operations into row-by-row computation.
• The optimizer cannot see inside the function body, treating it as a black box. This prevents many optimizations and accurate cardinality estimates.
• In many SQL Server versions, the presence of scalar functions in the SELECT or WHERE list can disable parallelism, forcing single-threaded plans for otherwise parallelizable queries.
• They frequently increase CPU usage and query duration dramatically on large datasets.

Mitigations include:

• Refactoring to inline TVFs or pure set-based SQL.
• Replacing scalar functions with computed columns (possibly indexed) when appropriate.
• Inlining logic into the main query where feasible.

A view is a named, virtual table defined by a SELECT query. It does not store data itself (unless indexed) but presents a reusable query abstraction.

Benefits:

• Abstraction and simplification: Hides complex joins and expressions behind a simple interface.
• Security: Exposes only selected columns/rows while hiding underlying schemas and tables.
• Reuse: Centralizes business logic or filter logic so that many queries can benefit from a single definition.
• Schema evolution: Application code can query the view even if underlying tables change, as long as the view's contract is preserved.

Limitations:

• Views can become stacked (views on views), leading to overly complex, hard-to-tune execution plans.
• Not all views are updatable; complex joins, aggregates, and DISTINCT can prevent direct DML.
• They do not inherently improve performance unless combined with indexed views or used to encapsulate optimal query patterns.

Indexed views materialize the result of a view and maintain it on disk like a physical table. They are backed by a clustered index and optionally additional nonclustered indexes.

When to use:

• Heavy, repetitive aggregations or joins over large datasets (e.g., reporting queries over transactional tables).
• Scenarios where read performance is critical and data changes are relatively moderate.
• To precompute expensive expressions and reduce CPU usage under analytic workloads.

Trade-offs:

• Every insert/update/delete on the underlying tables must update the indexed view, increasing DML overhead.
• Strict requirements apply (e.g., schema binding, deterministic expressions).
• Can complicate troubleshooting if developers are unaware of their presence.

They are powerful for read-intensive workloads but should be used selectively and measured carefully in write-heavy systems.

Triggers are special stored procedures that automatically execute in response to DML events (INSERT, UPDATE, DELETE) or certain DDL operations.

AFTER triggers:

• Fire after the base operation logically succeeds but before the transaction commits.
• Often used for auditing, logging, or enforcing complex constraints that span multiple tables.
• Run within the transaction, so failures can cause rollbacks and increase latency.

INSTEAD OF triggers:

• Fire instead of the original DML operation.
• Commonly used on views to simulate complex update logic or route changes to multiple underlying tables.
• Give full control over how changes are applied.

Both types must be designed carefully to avoid recursion, hidden performance issues, and unexpected side effects.

Triggers are powerful but can introduce hidden complexity and performance problems in large systems.

Key concerns:

• Hidden behavior: Business logic executes implicitly on DML, making the system harder to reason about. Developers may not realize why certain actions occur.
• Chained effects: Triggers can call other triggers, causing cascading side effects that are difficult to debug and test.
• Performance impact: Trigger logic runs inside the transaction. Long-running triggers increase lock durations and contention.
• Maintainability: Logic spread across triggers and procedures leads to fragmented business rules and operational risk.

For these reasons, many teams prefer stored procedures, constraints, and explicit application-level logic over heavy trigger usage, reserving triggers for focused, unavoidable use cases (e.g., auditing).

Window functions (e.g., ROW_NUMBER(), RANK(), SUM() OVER (...)) allow calculations across sets of rows without collapsing them into a single result like GROUP BY does.

Why they are essential:

• Enable ranking, running totals, moving averages, percentiles, and gap/density analysis in a single pass.
• Reduce the need for self-joins and correlated subqueries, often resulting in cleaner and faster plans.
• Can be combined with PARTITION BY and ORDER BY to support rich analytical queries directly in OLTP or reporting databases.
• Help keep logic set-based and push computation into the database layer where it is highly optimized.

Window functions operate over a logical window of rows, defined by PARTITION BY and ORDER BY clauses in the OVER() expression.

PARTITION BY:

• Divides the result set into groups (partitions) for independent calculations, similar to grouping in analytics.
• Example: SUM(SalesAmount) OVER (PARTITION BY CustomerId) gives total sales per customer.

ORDER BY:

• Defines the sequence of rows within each partition.
• Required for ranking and running calculations like ROW_NUMBER(), cumulative sums, and moving averages.

In summary, PARTITION BY defines the scope and ORDER BY defines the sequence of the window.

Table partitioning horizontally splits a large table into smaller, manageable partitions based on a partition key (often date or range-based IDs). Logically it remains a single table, but physically data is separated.

SQL Server implementation:

• Partition function: Defines boundary points for key ranges.
• Partition scheme: Maps partitions to one or more filegroups.
• The table or index is created ON the partition scheme, distributing rows across partitions based on the key.

Benefits:

• Maintenance operations (index rebuilds, statistics, archiving) can be targeted at specific partitions.
• Supports sliding window load/archive patterns via partition SWITCH operations.
• Can improve query performance via partition elimination, especially for time-sliced workloads.

Partitioning is not automatically faster; it must align with query predicates and maintenance strategies to be effective.

Partition elimination occurs when SQL Server restricts I/O to only those partitions that might contain relevant rows, instead of scanning all partitions.

Example: A table partitioned by OrderDate by month. A query filtered on OrderDate BETWEEN '2024-01-01' AND '2024-01-31' can read only the January partition if:

• The filter is directly on the partition key.
• There are no non-SARGable expressions on the partition column.
• The data types and collation match exactly.

If functions like CONVERT() or different data types are used on the partition column, partition elimination may fail and all partitions may be scanned, losing the performance benefit.

Poor execution plans are usually symptoms of deeper issues in schema design, statistics, or query patterns.

Common causes:

• Missing or inappropriate indexes: Forcing table scans or expensive lookups.
• Stale or missing statistics: Leading to incorrect row estimates and wrong join strategies.
• Parameter sniffing: Plan optimized for one parameter value, reused for others with different data distributions.
• Scalar functions and multi-statement TVFs: Preventing optimization and parallelism.
• Complex views over views: Obscure actual data access and create bloated plans.
• Implicit conversions: Causing non-SARGable predicates or index misses.
• RBAR patterns (cursors, loops): Neglecting set-based approaches.

Effective tuning often involves query simplification, better indexing, and statistics maintenance rather than just tweaking server settings.

Parameter sniffing occurs when SQL Server compiles a plan using the initial parameter values it sees, then reuses that plan for subsequent executions. If data distribution is skewed, one plan may not fit all parameter scenarios.

Symptoms: Some calls are lightning fast, others very slow, using the same procedure and query shape.

Handling strategies:

• Use OPTION (RECOMPILE) for highly skewed queries where compilation cost is acceptable.
• Use OPTIMIZE FOR UNKNOWN or OPTIMIZE FOR (@param = ...) hints to choose more robust plans.
• Capture parameters in local variables inside the procedure to discourage sniffing and produce more average plans.
• Split logic into separate procedures for different parameter ranges if patterns are distinct.
• Use Query Store to force stable plans when appropriate.

SQL Server uses a plan cache to store compiled execution plans so that subsequent executions of the same (or parameterized) queries can skip the compilation phase.

Benefits:

• Reduces CPU overhead from repeated compilations.
• Improves response time for frequently executed queries and stored procedures.

Challenges:

• Poorly parameterized or ad-hoc queries can cause plan cache bloat, with many single-use plans.
• Parameter sniffing issues stem from plan reuse across different parameter values.
• Schema changes or statistics updates can invalidate plans, causing recompilation spikes.

Best practice is to use parameterized queries, monitor plan cache size and reuse patterns, and leverage Query Store to manage problematic plans.

Schema binding associates a view or function tightly with the underlying schema so that the referenced objects cannot be modified in incompatible ways without first changing the bound object.

Importance:

• Required for indexed views and some computed columns, ensuring structural stability.
• Prevents accidental changes (e.g., dropping or altering columns) that would silently break dependent logic.
• Helps enforce contracts between application code, views, and functions.

In high-governance environments, schema binding is a tool to ensure that refactoring is deliberate and fully impact-assessed.

SQL is fundamentally a set-based language: it is designed to operate on collections of rows at once, not one row at a time.

Set-based operations:

• Leverage the optimizer to choose efficient algorithms (hash joins, merges, batch operations).
• Use fewer logical and physical reads for bulk operations.
• Scale more gracefully as data volume grows.

Row-by-row (RBAR) operations:

• Implement logic via cursors or loops, processing one row at a time.
• Usually lead to excessive context switching, locking overhead, and long runtimes.
• Only justified for very complex, inherently procedural business rules.

Senior-level SQL design focuses on transforming requirements into set-based patterns whenever possible, often with window functions, joins, and properly designed queries.

Cursors are database objects that allow row-by-row traversal of a result set, similar to iterators in procedural languages.

Reasons they are discouraged:

• They process data one row at a time, leading to poor performance on large sets.
• They often hold locks for a long time, reducing concurrency.
• They introduce complex, procedural logic that is harder to maintain and test.
• They typically have higher memory and tempdb overhead compared to set-based alternatives.

Cursors should be a last resort, used only when set-based solutions are impractical or impossible. In many cases, window functions, MERGE statements, or carefully written set-based updates can replace cursor logic.

These metrics help diagnose I/O and memory performance in SQL Server.

Logical reads: The number of 8KB pages read from the buffer cache (memory). High logical reads can indicate inefficient queries or missing indexes.

Physical reads: Pages read from disk because they were not found in the buffer cache. Physical I/O is orders of magnitude slower than memory access.

Page Life Expectancy (PLE): An indicator of how long pages stay in the buffer cache before being evicted. A consistently low PLE suggests memory pressure or inefficient queries repeatedly flushing the cache.

Senior engineers use these metrics together to determine whether to focus on query tuning, indexing, or adding memory/storage capacity.

SARGability (Search ARGument-ability) describes whether a predicate can efficiently use an index to seek rows instead of scanning.

SARGable predicates:

• Simple comparisons like Column = @Value, Column >= @Start AND Column <= @End.
• Allow the optimizer to perform index seeks and range scans.

Non-SARGable patterns:

• Applying functions to columns: WHERE LOWER(Col) = 'abc'.
• Expressions on the left side: WHERE Col + 1 = 10.
• Implicit conversions that change the column's data type.
• Leading wildcards: LIKE '%abc'.

Ensuring predicates are SARGable is one of the most impactful techniques in query tuning: it allows indexes to be used effectively, minimizing reads and dramatically improving performance.

TempDB is SQL Server’s global workspace used by all users and internal operations. It is essential because it handles:

• Sorting operations (ORDER BY, GROUP BY)
• Hash joins and aggregations
• Row versioning for snapshot isolation
• Temporary tables and table variables
• Intermediate spill data during execution
• Cursors and internal worktables

If TempDB becomes slow or suffers contention, the entire SQL Server instance slows down. Proper sizing, fast storage, and multiple data files are critical for performance.

TempDB contention occurs when multiple threads compete for the same allocation pages (PFS, GAM, SGAM) or metadata structures.

Typical causes include:

• Too few TempDB data files
• Heavy use of temp tables/table variables
• Large sorting or hashing operations
• High row versioning pressure

Fixes: increase file count, equal-size files, optimize workload, reduce spills.

SQL Server stores all data in 8 KB pages.

• Data pages store full table rows.
• Index pages store B-tree navigation structures and pointers.

Understanding pages explains index fragmentation, logical reads, and physical I/O behavior.

The buffer pool is SQL Server’s memory area used for caching data and index pages.

• Logical reads come from memory.
• Physical reads occur only when data is missing in cache.
• High buffer reuse improves performance.

If buffer pool is too small: more disk reads, lower PLE, and slower query execution.

Page Life Expectancy (PLE) measures how long pages remain in the buffer pool before eviction.

High PLE = good memory health.
Low PLE = memory pressure, excessive physical reads.

Common causes of low PLE: bad queries, missing indexes, large scans, spills.

The transaction log is an append-only structure that records all modifications. SQL Server uses Write-Ahead Logging (WAL):

• Log records are written to disk first
• Data pages update later

This ensures atomicity and durability. Log truncation depends on checkpoints and active transactions.

Checkpoints flush dirty pages (modified pages) from memory to disk.

Benefits:

• Shorter crash recovery time
• Enables transaction log truncation
• Reduces buffer pool pressure

Dirty pages: modified in buffer pool but not yet persisted to disk.
Clean pages: identical to the disk version.

Checkpoints convert dirty pages to clean pages. Too many dirty pages increase recovery time and degrade performance.

Latches protect internal memory structures and are non-transactional.

Locks protect logical data consistency and last for transaction duration.

Latch waits = engine pressure.
Lock waits = concurrency or blocking issues.

Lock escalation converts many row/page locks into a single table lock to reduce overhead.

Impact:

• Fewer locks = lower memory use
• But higher blocking risk

The Cardinality Estimator (CE) predicts row counts used to choose join types, memory grants, and access paths.

Poor estimates ? poor execution plans.

CE depends on statistics, data distribution, and query structure.

Statistics describe data distribution that SQL Server uses to estimate row counts.

Bad or outdated stats ? misestimation ? poor plans ? slow queries.

SQL Server allocates memory for sorting, hashing, and aggregations.

Incorrect grants:

• Too small ? spills to TempDB
• Too large ? resource starvation

A spill happens when the memory grant is too small and SQL Server must offload intermediate data to TempDB.

Common causes: inaccurate cardinality estimation, heavy sorts, hash joins, window functions.

The Query Processor:

• Parses T-SQL
• Binds objects
• Optimizes using cost-based rules
• Generates physical plans

Logical operators represent conceptual operations (join, project, filter).

Physical operators are real engine implementations (hash join, nested loop, merge join).

Join selection depends on:

• Row count estimates
• Sort order
• Indexes
• Estimated CPU and I/O cost

A hash match creates an in-memory hash table for joins and aggregates.

If memory is insufficient ? spills to TempDB ? huge slowdown.

Changes go to an in-memory log buffer. A log flush occurs:

• On transaction commit
• When buffer fills
• On checkpoint

Slow log storage = slow commits.

Row store: row-by-row storage; best for OLTP.

Column store: column-by-column storage; best for analytics, high compression, parallel execution.