SQL interview questions

Database Indexes: B-Trees, Seeks, and Plan Analysis

Answer

• Database indexes are balanced search trees (B-trees) made up of 8 KB data pages. The root node points to intermediate levels, which point down to leaf pages, allowing the database engine to find specific rows in logarithmic time O(log N) instead of scanning the full table O(N).
• Clustered Index: The leaf level of a clustered index contains the actual physical table data pages. A table can have only one clustered index because rows can be physically sorted on disk in only one order. A table without a clustered index is a Heap.
• Nonclustered Index: A nonclustered index has its own B-tree separate from base table data. The leaf level contains index key columns and a row locator. On a clustered table, the row locator is the clustered index key. On a heap, the row locator is an 8-byte RID (Row Identifier: File:Page:Slot).
• The Tipping Point and Key Lookups: If a query filters on an index but selects columns not included in that index, SQL Server performs a Key Lookup to the clustered index for each matching row. If the number of matching rows exceeds approximately 25-33% of the table pages, the query optimizer abandons the index seek entirely and forces a clustered index scan or table scan because random I/O from thousands of lookups costs more than reading the entire table sequentially.

SQL (SQL Server)
-- 1. Non-SARGable query (Triggers full Index Scan / Table Scan)
SELECT OrderId, CustomerId, TotalAmount
FROM dbo.Orders
WHERE ISNULL(Status, 'Pending') = 'Shipped' -- Function wrapping column breaks B-tree seek
  AND OrderDate >= '2026-01-01';

-- 2. SARGable rewrite (Enables high-efficiency Index Seek)
SELECT OrderId, CustomerId, TotalAmount
FROM dbo.Orders
WHERE (Status = 'Shipped' OR (Status IS NULL AND 'Pending' = 'Shipped'))
  AND OrderDate >= '2026-01-01';

-- 3. Production Covering Index:
-- CustomerId and OrderDate are seek keys; TotalAmount is included to eliminate Key Lookups
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate)
INCLUDE (TotalAmount, Status)
WITH (DATA_COMPRESSION = PAGE);

Covering Indexes and the INCLUDE Clause

• A covering index contains all the columns requested by a query (both in WHERE/JOIN and SELECT), allowing the database engine to resolve the query entirely within the nonclustered index leaf pages with zero Key Lookups.
• Index Keys vs INCLUDE Columns: Columns in the main key list are stored at all levels of the B-tree, enforcing sort order and index size limits (maximum 1,700 bytes for nonclustered keys in SQL Server). Columns added via INCLUDE exist only on leaf-level pages and do not participate in sorting or tree navigation.
• Best Practice: Put equality and inequality filter columns in the index key (ordered by selectivity and join keys), and place columns needed purely for SELECT into the INCLUDE clause.

SARGability (Search Argument Ability)

• A query is SARGable if the database engine can isolate a seek range along the B-tree keys without evaluating functions or type conversions against every row.
• Function Trap: WHERE YEAR(CreatedAt) = 2024 is non-SARGable because the database must evaluate the YEAR() scalar function on every single row in the table, resulting in a full index scan. SARGable rewrite: WHERE CreatedAt >= '2024-01-01' AND CreatedAt < '2025-01-01'.
• Implicit Conversion Trap: Filtering an NVARCHAR parameter against a VARCHAR column forces SQL Server to convert the column data type for every row according to data type precedence rules, destroying index seek capabilities and causing high CPU utilization.

Joins

Answer

• INNER JOIN keeps rows that have matching join keys in both tables.
• LEFT OUTER JOIN keeps all left rows and fills nulls where the right side has no match. RIGHT and FULL OUTER are the mirrors and union forms.
• Always write an ON predicate for the join keys. Missing ON (or joining with a constant true) yields a Cartesian product.
• Join algorithms include nested loops, hash join, and merge join. Indexes and sorted inputs influence which plan appears.
• Filter early with WHERE on the driving tables when predicates do not need the other side. Keep join predicates in ON for outer joins so you do not turn them into inner joins by accident.
• Verify join cardinality with the actual execution plan. Unexpected row explosions often mean a many-to-many key match.
• Pitfall: joining on nullable keys or mismatched types can drop matches or force scans and conversions.
• Prefer equijoins on indexed keys for OLTP. Document intentional cross joins when they are required.
• In EF Core and ADO.NET, inspect the generated SQL so accidental joins or missing filters do not hide in the ORM.

SQL
SELECT c.Name, o.OrderDate, o.Total
FROM dbo.Customers c
INNER JOIN dbo.Orders o ON o.CustomerId = c.Id
WHERE o.OrderDate >= '2025-01-01';

N+1 queries

Answer

• N+1 means one query loads parents, then one extra query runs per parent to load children. Round trips dominate cost.
• Fix in SQL with a join, a single IN list or JOIN fetch, or a batched second query that loads all children at once.
• In EF Core, eager load with Include / ThenInclude, or use split queries when one wide join becomes too heavy.
• Projection to DTOs with a shaped Select can load only needed columns and related collections in fewer queries.
• Lazy loading hides the pattern until production traffic multiplies the per-row queries.
• Detect with SQL profiler, MiniProfiler, Application Insights, or logging of parameterized SQL in staging.
• Pitfall: fixing with a Cartesian join that multiplies parent rows and breaks paging or aggregates.
• Batch size limits matter. Huge IN lists may need chunking.
• State the symptom (query count grows with parent count) and the fix (join or batch) in interviews.

EF Core
var orders = await db.Orders
    .Include(o => o.Lines)
    .Where(o => o.CustomerId == id)
    .ToListAsync();

Query optimization

Answer

• Start from the actual execution plan: seeks versus scans, join types, sorts, and key lookups.
• Add or adjust indexes for filter, join, and order columns. Covering INCLUDE columns avoid lookups.
• Select only needed columns. Avoid SELECT * on wide tables in hot paths.
• Fix N+1 patterns with joins or batched loads. Keep transactions short to reduce locking.
• Write sargable predicates so indexes can seek. Avoid wrapping filter columns in functions when a range on the bare column works.
• Update statistics after large data changes. Use query hints only after measuring alternatives.
• In EF Core, project to DTOs, use AsNoTracking for read-only queries, and watch for client-side evaluation.
• Measure with realistic data volumes. Tiny sample tables hide plan changes that appear in production.
• Pitfall: adding indexes for every slow query until write latency and storage cost rise without a review of unused indexes.

SQL
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_CustomerId
ON dbo.Orders(OrderDate, CustomerId)
INCLUDE (Total);

SELECT CustomerId, Total
FROM dbo.Orders
WHERE OrderDate >= '2025-01-01';

DELETE vs TRUNCATE vs DROP

Answer

• DELETE removes rows. You can use WHERE. It is fully logged for each row (typical SQL Server behavior) and can run inside an explicit transaction with row-level undo.
• TRUNCATE TABLE removes all rows quickly. You cannot target individual rows with WHERE. Logging is minimal compared with deleting every row.
• DROP removes the table (or other object) itself, including its data and dependent objects as defined by the engine.
• TRUNCATE requires appropriate permissions and cannot truncate tables referenced by foreign keys in the usual SQL Server setup without addressing those constraints.
• Identity reseeding behavior differs: TRUNCATE typically reseeds identity. DELETE does not unless you reseed explicitly.
• DELETE can fire DELETE triggers. TRUNCATE does not fire those row DELETE triggers.
• Pitfall: running TRUNCATE or DROP in production without a backup or transaction plan.
• Prefer DELETE with WHERE for selective cleanup. Prefer TRUNCATE for emptying a staging table you own.
• Confirm details for your SQL Server version when permissions, replication, or temporal tables are involved.

SQL
DELETE FROM Orders WHERE Status = 'Cancelled';
-- TRUNCATE TABLE StagingOrders;
-- DROP TABLE StagingOrders;

WHERE vs HAVING

Answer

• WHERE filters rows before groups form. It cannot reference aggregate results of that GROUP BY.
• HAVING filters groups after GROUP BY, often using aggregates such as COUNT(*) > 1 or SUM(Amount) > 1000.
• Push predicates to WHERE when they do not need aggregates. That reduces rows before grouping and usually helps performance.
• Logical order of a SELECT: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT list, ORDER BY (engine may rearrange physically).
• You can use HAVING without GROUP BY for a single group over the whole filtered set, but WHERE is still preferred for non-aggregates.
• Pitfall: putting a non-aggregate filter only in HAVING, which forces grouping work on rows you could have dropped earlier.
• Aliases defined in the SELECT list are not available in WHERE on SQL Server. Repeat the expression or use a CTE.
• Interview tip: give one query that must use HAVING and one that should use WHERE instead.
• Combine both: WHERE restricts base rows, GROUP BY builds groups, HAVING keeps qualifying groups.

SQL
SELECT CustomerId, COUNT(*) AS OrderCount
FROM Orders
WHERE CreatedAt >= '2026-01-01'
GROUP BY CustomerId
HAVING COUNT(*) >= 5;

UNION vs UNION ALL

Answer

• UNION combines result sets and removes duplicate rows across the combined output.
• UNION ALL keeps duplicates and usually costs less because it skips the distinct sort or hash.
• Column counts must match. Types must be compatible. Names come from the first SELECT.
• ORDER BY applies to the final combined result and typically sits after the last SELECT.
• Prefer UNION ALL when you know the sets are disjoint or when duplicate removal is not required.
• Prefer UNION when callers must see distinct rows and you accept the extra distinct work.
• Pitfall: using UNION by habit on large sets and paying for distinct every time.
• NULL handling in duplicate removal follows SQL equality rules for UNION distinctness.
• CTEs and derived tables often wrap UNION ALL branches for clearer planning and filtering.

SQL
SELECT Email FROM Customers
UNION ALL
SELECT Email FROM Leads;

VARCHAR vs NVARCHAR

Answer

• VARCHAR stores non-Unicode characters. Storage and comparison follow the database or column collation.
• NVARCHAR stores Unicode as UTF-16 on SQL Server and handles wider character sets (many languages and symbols).
• NVARCHAR uses more storage per character (typically 2 bytes per UCS-2/UTF-16 code unit). Pick it when you need Unicode data.
• Comparing VARCHAR and NVARCHAR can cause implicit conversions that prevent index seeks. Keep types consistent across joins and parameters.
• Prefix string literals with N for NVARCHAR literals in T-SQL (N'Ada') so Unicode characters are preserved.
• MAX types (VARCHAR(MAX), NVARCHAR(MAX)) support large values with different storage and some indexing limits.
• Pitfall: storing Unicode in VARCHAR and losing or replacing characters silently under the wrong collation.
• For new global apps on SQL Server, NVARCHAR is the usual default unless you have a measured storage reason not to.
• Document collation choices. Case and accent sensitivity affect uniqueness and searches independently of VARCHAR versus NVARCHAR.

SQL
CREATE TABLE People (
  Id INT PRIMARY KEY,
  Email VARCHAR(320) NOT NULL,          -- typically ASCII
  DisplayName NVARCHAR(200) NOT NULL    -- Unicode-safe
);

CTEs

Answer

• A common table expression (WITH name AS (query)) names a result set for one statement that follows.
• Improves readability for multi-step queries. You can reference the CTE like a table in the outer SELECT, INSERT, UPDATE, or DELETE.
• Multiple CTEs can appear in one WITH list, separated by commas, and later CTEs can reference earlier ones.
• Recursive CTEs use UNION ALL between an anchor member and a recursive member to walk hierarchies (org charts, graphs of edges).
• Unlike temp tables, a non-recursive CTE is not a persisted temp object. The optimizer may inline or materialize it as it chooses.
• Pitfall: assuming a CTE always materializes once. The engine may evaluate it more than once unless you use temp tables or other patterns.
• Filter early inside the CTE when that clarifies the steps and reduces work for the outer query.
• Recursive CTEs need a termination path. Add depth guards for deep or cyclic data.
• Interview tip: contrast CTEs with derived tables and temp tables for readability, reuse, and indexing.

SQL
WITH OpenOrders AS (
  SELECT Id, CustomerId, Total
  FROM Orders
  WHERE Status = 'Open'
)
SELECT CustomerId, SUM(Total) AS OpenTotal
FROM OpenOrders
GROUP BY CustomerId;

Normalization

Answer

• 1NF: store atomic values in columns and avoid repeating groups or multi-valued cells in one column.
• 2NF: every non-key attribute depends on the whole composite key, not only part of it.
• 3NF: non-key attributes do not depend on other non-key attributes (no transitive dependency on the key).
• Higher forms (BCNF and beyond) tighten key rules further. Most OLTP designs aim at 3NF or BCNF for operational tables.
• Normalization reduces update anomalies and duplicate facts. The same fact lives in one place.
• Denormalize deliberately for read-heavy reporting or performance, with clear ownership of how copies stay in sync.
• Pitfall: accidental denormalization (copying customer name onto every order without a rule) causes inconsistent updates.
• Foreign keys enforce relationships that normalized designs introduce between tables.
• Interview tip: give a concrete 2NF or 3NF violation and the table split that fixes it.

SQL
-- Instead of repeating customer columns on every order line,
-- keep Customers and Orders related by key.
CREATE TABLE Customers (
  Id INT PRIMARY KEY,
  Name NVARCHAR(200) NOT NULL
);
CREATE TABLE Orders (
  Id INT PRIMARY KEY,
  CustomerId INT NOT NULL REFERENCES Customers(Id),
  OrderDate DATE NOT NULL
);

Window functions

Answer

• Window functions compute values across a related set of rows without collapsing those rows into one group output row.
• OVER (PARTITION BY ... ORDER BY ...) defines the window peer set and optional ordering inside each partition.
• ROW_NUMBER assigns unique ranks per partition. RANK and DENSE_RANK handle ties differently.
• LEAD and LAG read neighboring rows in the ordered window. Useful for deltas and previous-value comparisons.
• SUM, AVG, and COUNT can run as windows for running totals when you add a frame clause (ROWS or RANGE).
• Often clearer than correlated subqueries for rankings, top-n-per-group, and period-over-period columns.
• Filter on window results in an outer query or CTE. WHERE cannot reference the window alias in the same SELECT level.
• Pitfall: forgetting PARTITION BY and ranking the entire result as one partition.
• Supported in modern SQL Server and ANSI SQL. Confirm frame defaults when running totals look wrong.

SQL
SELECT OrderId, Total,
       ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
FROM dbo.Orders;

ACID Properties and Database Transaction Isolation Levels

Answer

• A transaction is a logical unit of work that must exhibit four fundamental ACID properties: Atomicity (all-or-nothing completion), Consistency (data invariants preserved across commits), Isolation (concurrent transactions execute without interfering with one another), and Durability (committed updates survive server crashes and power failures via the Write-Ahead Log).
• The ANSI SQL Isolation Levels dictate how the database engine balances data consistency against concurrent throughput by controlling read locks and versioning.

SQL (Setting Isolation Levels in SQL Server)
-- Example 1: Strict financial ledger transaction requiring Serializable isolation
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

SELECT Balance FROM dbo.Accounts WHERE AccountId = @FromId;
UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromId;
UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountId = @ToId;

COMMIT TRANSACTION;

-- Example 2: Enabling Read Committed Snapshot Isolation (RCSI) at the database level
ALTER DATABASE CurrentDatabase 
SET READ_COMMITTED_SNAPSHOT ON 
WITH ROLLBACK IMMEDIATE;

Concurrency Anomalies and Isolation Levels

• Dirty Read: Transaction A reads data modified by Transaction B before Transaction B commits. If B rolls back, A read data that never technically existed. Prevented by Read Committed.
• Non-Repeatable Read: Transaction A reads a row, Transaction B updates that row and commits, and Transaction A reads the same row again to find modified values. Prevented by Repeatable Read (which holds shared locks on read rows until transaction completion).
• Phantom Read: Transaction A queries a range of rows (e.g. WHERE Age > 30), Transaction B inserts a new row matching that range and commits, and Transaction A re-runs the range query to find "phantom" rows. Prevented by Serializable (which uses Key-Range locks to freeze the entire gap between index keys).
• Snapshot Isolation (RCSI): Uses row versioning in tempdb. Readers do not acquire shared locks and writers do not block readers. Readers see a transaction-consistent snapshot of data committed before the query or transaction began.

SQL Server Execution Plans and Join Operators

Answer

• An Execution Plan is the compiled physical roadmap generated by the SQL Server Cost-Based Optimizer determining how a declarative SQL query will be executed against physical database storage.
• Estimated vs Actual Execution Plans: An Estimated Plan is compiled based on statistics and cardinality estimates without running the query. An Actual Execution Plan executes the query and includes runtime statistics: actual row counts, execution count, CPU time, and operator memory grants.
• Cardinality Estimation (CE): The optimizer estimates how many rows will pass through each operator using column distribution statistics and histograms. When statistics are outdated, the optimizer severely underestimates or overestimates row counts, selecting sub-optimal join algorithms or undersizing memory grants (causing tempdb spills).

T-SQL (Inspecting Plan XML and Memory Grants)
-- View active query execution plans and memory grants
SELECT 
    r.session_id,
    r.cpu_time,
    r.total_elapsed_time,
    mg.granted_memory_kb,
    mg.ideal_memory_kb,
    t.text AS sql_text,
    p.query_plan AS execution_plan
FROM sys.dm_exec_requests r
LEFT JOIN sys.dm_exec_query_memory_grants mg ON r.session_id = mg.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p;

Core Physical Join Operators

• Nested Loops Join: For each row in the outer input, SQL Server searches the inner input. Extremely fast when the outer input is small and the inner input has a highly selective index seek. Degrades to O(N * M) if the outer input is large and unindexed.
• Hash Match Join: Used for large, unsorted, unindexed inputs. SQL Server builds an in-memory hash table on the join key of the smaller table (Build phase), then scans the larger table to probe matching hash buckets (Probe phase). Requires memory grants; if memory is insufficient, it spills to tempdb, degrading performance.
• Merge Join: The most efficient join operator for large datasets, but strictly requires that both inputs are already sorted on the join key (for example, via clustered index scans or explicit sorts). Reads both streams concurrently in a single sequential pass O(N + M).

SQL Server Deadlocks and Resolution Strategies

Answer

• A deadlock occurs when two or more transactions hold exclusive or shared locks on separate resources, and each transaction requests a lock on the other's held resource, creating a circular wait condition that neither can escape.
• Deadlock Detection: SQL Server runs an internal deadlock detection thread (every 5 seconds, or more frequently when deadlocks occur). It detects the cycle in the lock graph, selects the transaction that is least expensive to rollback as the Deadlock Victim, terminates it with SQL Error 1205, and rolls back its changes.
• Common Deadlock Patterns: (1) Reverse Object Order: Transaction A updates Table1 then Table2; Transaction B updates Table2 then Table1. (2) Key Lookup Deadlock: A query takes a shared lock on a nonclustered index and needs a clustered row lock, while a concurrent UPDATE holds the clustered lock and requests the nonclustered index lock.

SQL (Enabling RCSI to Prevent Reader-Writer Deadlocks)
-- Enable Read Committed Snapshot Isolation (RCSI)
-- Eliminates shared locks for SELECT queries, preventing reader-writer deadlocks
ALTER DATABASE CurrentDatabase
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

-- Application Level: Transient Error 1205 Catch and Retry in C#
public async Task ExecuteWithDeadlockRetryAsync(Func<Task> action, int maxRetries = 3)
{
    for (int attempt = 1; attempt <= maxRetries; attempt++)
    {
        try
        {
            await action();
            return;
        }
        catch (SqlException ex) when (ex.Number == 1205 && attempt < maxRetries)
        {
            await Task.Delay(TimeSpan.FromMilliseconds(100 * Math.Pow(2, attempt)));
        }
    }
}

Deadlock Prevention and Mitigation

• Consistent Access Order: Enforce a strict ordering convention across all stored procedures and application code: always access and update tables in the exact same alphabetical or dependency sequence.
• Snapshot Isolation: Enabling READ_COMMITTED_SNAPSHOT (RCSI) eliminates reader-writer blocking. Readers read row versions from tempdb without acquiring shared locks, preventing readers and writers from deadlocking each other.
• Application Retry Policy: In high-concurrency systems, transient deadlocks are inevitable. The application layer must wrap database operations in an exponential backoff retry loop targeting SQL Error Number 1205.

Advanced: sargable predicates

Answer

• A predicate is sargable when the optimizer can seek an index on the column instead of scanning and computing expressions per row.
• Wrapping the column blocks a normal seek: YEAR(OrderDate) = 2025, LEFT(Name, 3) = 'Ada', Amount + 1 = 10.
• Write ranges on the bare column: OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'.
• LIKE '%foo' cannot use a normal btree seek on Name. LIKE 'foo%' often can as a range seek.
• Implicit conversions on the column (for example comparing VARCHAR column to NVARCHAR parameter) can also block seeks.
• Computed persisted columns or indexed computed columns can restore seekability when an expression is required.
• Pitfall: applying functions for readability in hot WHERE clauses without checking the plan.
• Verify with the actual plan: seek plus residual predicate is usually better than a full scan on large tables.
• Teach the habit: keep the indexed column alone on one side of the comparison when possible.

SQL
-- Not sargable
SELECT *
FROM dbo.Orders
WHERE YEAR(OrderDate) = 2025;

-- Sargable
SELECT *
FROM dbo.Orders
WHERE OrderDate >= '2025-01-01'
  AND OrderDate < '2026-01-01';

Advanced: isolation anomalies

Answer

• Read uncommitted can return dirty data from another open transaction that may still roll back.
• Read committed blocks dirty reads. Non-repeatable reads and phantoms remain possible under locking read committed.
• A non-repeatable read happens when a row you read changes before you read it again in the same transaction.
• A phantom is a new row that appears in a previously read range (or a row that disappears) under concurrent inserts or deletes.
• In SQL Server, repeatable read keeps read rows stable but phantoms can still appear. Serializable prevents that class of anomaly and costs more locking or versioning work.
• Snapshot and read committed snapshot (RCSI) use row versions so readers do not block writers the same way. Writers can still hit update conflicts under snapshot.
• Pitfall: assuming READ COMMITTED alone gives a stable report across multiple statements. It does not.
• Choose isolation for the anomaly you must prevent, then measure blocking and throughput.
• State dirty read, non-repeatable read, and phantom clearly in interviews, then map them to common SQL Server levels.

SQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
SELECT Balance FROM dbo.Accounts WHERE Id = 1;
-- another session may change Balance before you read again
SELECT Balance FROM dbo.Accounts WHERE Id = 1;
COMMIT;