Ultimate Advanced SQL Interview Mastery Guide – 200+ Expert-Level Questions, Detailed Answers & Performance Secrets | FreeLearning365

Q: What is FreeLearning365?

FreeLearning365 is a free learning platform offering daily tutorials on technology, programming, and certifications.

Question

1 What is query rewriting?

Answer 1

Detailed Answer: Query rewriting means transforming a logical SQL query into a semantically equivalent form that executes faster. It’s a core skill of an expert developer – you restructure subqueries, joins, and predicates to give the optimizer a better input. For example, replacing WHERE col IN (SELECT ...) with an INNER JOIN often reduces nested-loop overhead.

-- Before rewriting
SELECT * FROM Orders WHERE CustomerID IN (SELECT ID FROM Customers WHERE Country = 'DE');

-- After rewriting
SELECT o.* FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.ID WHERE c.Country = 'DE';

📊 Impact: Reduced logical reads by 60% and eliminated a table spool in the plan.

Answer 2

Detailed Answer: Use EXISTS when you only need a boolean check on the existence of matching rows, especially on large, unindexed child tables – it can short-circuit and stop scanning after the first match. Use JOIN when you need columns from both sides or the join key is heavily indexed and the result set is small.

💡 Interview Tip: “I prefer EXISTS for anti/semi-join patterns because it clearly expresses intent and avoids duplicate rows, while JOIN is better for denormalized reporting where I need related attributes.”

Answer 3

Detailed Answer: SQL engines are designed to process entire sets of data at once, leveraging parallelism, indexing, and join algorithms. Cursors or row-by-row loops break this model, forcing context switches and log flushes. Expert developers always default to set-based operations (window functions, batch updates, merge) and fall back to procedural approaches only when complex business rules require stateful processing that cannot be vectorised.

⏱️ Real‑world metric: A row-by-row update on 1M rows took 45 minutes; a single set-based UPDATE with a CASE expression finished in 8 seconds.

Answer 4

An anti-join returns rows from one table that have no match in another. The most performant pattern is NOT EXISTS with a correlated subquery, because it can use a semi-join operator and stop early. LEFT JOIN ... WHERE right.key IS NULL is also common but often results in a full join and a filter, which is less efficient.

SELECT * FROM Customer c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.ID);

Answer 5

A semi-join returns rows from one table that do have a match in another, without duplicating them. Again, EXISTS is the canonical implementation. Some databases support an explicit SEMI JOIN in execution plans, which can be forced by using EXISTS or IN with a properly indexed subquery. Example: “Get all customers who placed an order in 2025” – EXISTS ensures each customer appears once.

Answer 6

Duplicate joins occur when the same table is joined multiple times for different conditions. Prevention: consolidate with CASE aggregations, derived tables/CTEs that calculate all metrics in one pass, and guarantee uniqueness with composite keys and GROUP BY.

SELECT CustomerID,
       SUM(CASE WHEN Status = 'Pending' THEN Amount ELSE 0 END) AS PendingTotal,
       COUNT(CASE WHEN Status = 'Shipped' THEN 1 END) AS ShippedCount
FROM Orders GROUP BY CustomerID;

Result: Eliminated 4 redundant joins, reduced IO by 75%.

Answer 7

A scalar subquery returns exactly one column and one row – it can be used inside SELECT or WHERE. A table subquery (derived table/CTE) returns a full result set. Scalar subqueries are dangerous because if they return multiple rows the query will error; also they often force row-by-row execution. Experts avoid them in the SELECT list of large queries and use CROSS APPLY or pre-aggregated joins instead.

Answer 8

CROSS APPLY acts like an inner join to a table-valued function or subquery – rows are returned only when the right side produces a result. OUTER APPLY is analogous to a left join, preserving the left row with NULLs if no match. They’re invaluable for calling a function per row (e.g., parsing JSON, STRING_SPLIT) or selecting top-N per group.

Answer 9

A lateral join is SQL’s standard way to allow a subquery in the FROM clause to reference columns from preceding tables. It’s the foundation of CROSS/OUTER APPLY in SQL Server and LATERAL in PostgreSQL/MySQL. It enables iterative patterns like unnesting arrays or complex top‑N analytics in a set‑based manner.

Answer 10

Recursive Common Table Expressions allow traversing hierarchical data (org charts, bill-of-materials) without procedural loops. They consist of an anchor member (base case) and a recursive member joined by UNION ALL. Termination control is vital – always include a depth counter to prevent infinite loops.

WITH RCTE AS (
    SELECT ID, ParentID, Name, 1 AS Level FROM Category WHERE ParentID IS NULL
    UNION ALL
    SELECT c.ID, c.ParentID, c.Name, r.Level + 1
    FROM Category c INNER JOIN RCTE r ON c.ParentID = r.ID
) SELECT * FROM RCTE;

Impact: Replaced 10 self-joins with a single recursive CTE, query time from 12s to 1.2s.

Answer 11

The frame clause (ROWS/RANGE BETWEEN ...) defines which rows the window function operates on relative to the current row. Default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for aggregate window functions, which can have surprising performance implications. Expert tuning involves specifying the tightest possible frame, e.g., ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING for a moving average.

Answer 12

ROWS works on physical position; RANGE works on logical values and includes peers (equal ORDER BY values). RANGE often requires a spool to group peers and can cause tempdb spill. Prefer ROWS unless the business logic requires peer grouping (e.g., treat all ties together).

Answer 13

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY key_columns ORDER BY (SELECT NULL)) rn
    FROM your_table
)
SELECT * FROM cte WHERE rn > 1;

This single‑pass set‑based method outperforms GROUP BY ... HAVING COUNT(*) > 1 when you need to delete or retrieve duplicates, and it works on massive tables when combined with proper indexing.

Answer 14

Skew occurs when a join key has a very high frequency (e.g., default value 'NULL' or a popular product). This overloads a single node. Solutions: broadcast hints, pre-aggregate the large side, or apply salting (add random prefix to the skewed key).

Impact: Join duration dropped from 2 hours to 4 minutes after salting.

Answer 15

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) rn
    FROM Orders
)
SELECT * FROM Ranked WHERE rn <= 3;

Answer 16

When pivot columns are unknown at design time (e.g., months), build SQL dynamically. Collect distinct values into a string, then construct a PIVOT or conditional aggregation query. In SQL Server, use STRING_AGG to build the list and sp_executesql with parameterization for safety. Always sanitize column names to avoid injection.

Answer 17

CTEs improve readability and can reference themselves (recursion), while derived tables exist only in the scope of a single FROM clause. CTEs in some databases (PostgreSQL) act as optimization fences, preventing push‑down. Expert choice: use CTEs for clarity and self‑documentation, but in critical performance paths test if inline derived tables give a better plan.

Answer 18

Query folding means the engine pushes filter and aggregation logic down to the base tables or view definitions before data moves through operators. It’s crucial in view and Power Query scenarios. An expert ensures that WHERE clauses on views are foldable (no functions on columns) and avoids computed expressions that break folding.

Answer 19

NULL does not equal NULL, so ordinary equality joins will drop rows with NULL keys. To safely join on nullable columns, use COALESCE or IS NOT DISTINCT FROM (ISO standard, supported in some DBs), or explicitly handle with OR (a.key IS NULL AND b.key IS NULL). Always communicate this nuance in interviews – it shows mastery of three-valued logic.

Answer 20

Problem: Monthly sales report joining 8 tables, taking 90 seconds.
Approach: Analyzed plan → found nested loops due to stale statistics and missing composite index. Rewrote using one CTE per grain, pre‑aggregated to the report grain, then joined aggregate results. Added a filtered index on date range.
Result: Query time dropped to 4 seconds, with 95% reduction in logical reads.

Answer 21

It’s the compiled set of physical operators (index seek, hash join, sort) that the database engine uses to execute a query. Reading plans reveals whether the optimizer chose efficient access methods, estimated row counts correctly, and where time is spent. Experts capture actual plans with runtime metrics (elapsed time, IO) to pinpoint bottlenecks.

Answer 22

The optimizer evaluates thousands of possible plan permutations and picks the one with the lowest estimated “cost” (a heuristic weighted sum of CPU and IO). It relies heavily on statistics and cardinality estimates. Understanding that it doesn’t guarantee the fastest plan – only the cheapest estimate – explains many performance surprises.

Answer 23

Cardinality estimation is the prediction of row counts at each step. If the estimate is off by orders of magnitude, the optimizer may choose a slow nested loop instead of a hash join, or allocate too little memory for a sort, causing spills. Multiple predicates, complex expressions, and table variables (no stats) are notorious for causing bad estimates.

Answer 24

They ripple through the plan: a nested loop on 1 expected row that actually drives 1 million rows causes crippling logical reads. Memory grants for sorts and hashes are based on estimates; underestimation leads to tempdb spills. Interviewers expect you to connect slow queries directly to estimation errors by looking at “Estimated vs Actual” in the plan.

Answer 25

Seek uses the B‑tree structure to go directly to the relevant rows – it’s ideal for selective predicates. Scan reads the entire index leaf level. A seek on a non‑selective predicate can still be slow if it covers millions of rows. The key is selectivity: seek + key lookup for single row vs. scan for large range.

Answer 26

A covering index includes all columns referenced in a query (in key and included columns). This eliminates the key lookup to the clustered index or heap, drastically reducing IO. An expert designs covering indexes for critical queries but avoids over‑indexing by ensuring they serve multiple queries via carefully ordered key columns.

Answer 27

Included columns (non‑key columns in the leaf level) allow you to cover queries without bloating the index’s search key. They don’t affect the B‑tree order, so they keep insertion and key size lean. Use them for payload columns like descriptions, amounts, or status flags that are only returned, not searched.

Answer 28

When a stored procedure is compiled, the optimizer creates and caches the plan based on the first parameter values seen. If those values are atypical (e.g., a wide date range), the cached plan may be horrible for subsequent executions. This can cause unpredictable performance – sometimes fast, sometimes extremely slow.

Answer 29

OPTION (RECOMPILE) – recompiles every time, perfect for small, high‑variance queries.
OPTIMIZE FOR (@param = typical_value) – stabilizes plan.
Use local variables to disguise values and force average plan.
Query rewriting with OPTION (OPTIMIZE FOR UNKNOWN).
For advanced cases, plan guides.

Interview approach: Explain the trade‑off between compilation overhead and plan stability.

Answer 30

A spool (table/index spool) stores intermediate results in tempdb, often for re‑reading rows needed multiple times (eager spool) or for recursive work. It can indicate a missing index or an unnecessary self‑join. Analyzing why a spool appeared often leads to rewriting the query to eliminate it and halve the execution time.

Answer 31

Hash join: builds a hash table on the smaller input; probes with the larger. Best for large unsorted inputs, requires memory. Merge join: requires both inputs sorted on the join key. Ideal when indexes already provide order. Without ordering, an expensive sort is added. Expert: knows when to force a merge join via index ordering rather than letting the optimizer sort.

Answer 32

Nested loops iterate the inner table for each outer row. It’s extremely fast for small outer set or when inner seek is very efficient (indexed). It becomes disastrous with large, unindexed inner scans. The rule: “small outer, big indexed inner.” Look for it in top‑N per group queries with CROSS APPLY.

Answer 33

The engine splits a query into multiple threads, each processing a subset of rows (typically via partitioning or range distribution). It dramatically speeds up large scans and aggregations. However, parallel plans consume more CPU and can be throttled by MAXDOP. Expert developers configure MAXDOP per workload, and sometimes force serial execution for OLTP queries to avoid worker contention.

Answer 34

DOP is the number of parallel threads per operator. Too high and you get excessive context switching and CXPacket waits; too low and the parallel benefit is lost. The server’s MAXDOP is a global setting, but you can override per query with OPTION (MAXDOP N). An expert correlates wait statistics (CXPACKET) with DOP settings.

Answer 35

Statistics describe data distribution (histograms, density) and are the optimizer’s only insight into table content. Auto‑update thresholds (e.g., 20% of data changed) can lag behind on large tables. Stale stats cause wrong cardinality estimates. A seasoned SQL developer manually updates statistics after large bulk loads and on partitioned tables.

Answer 36

Fragmentation (logical scan fragmentation) means physical page order no longer matches the index key order, causing extra IO during range scans. For spinning disks, high fragmentation (>30%) slows sequential reads. For SSD/NVMe, the impact is smaller but still affects read‑ahead efficiency. Monitor via sys.dm_db_index_physical_stats.

Answer 37

REBUILD recreates the index entirely – it defragments fully, reclaims space, updates statistics, and resets fillfactor. It’s an offline operation (unless ONLINE) and requires extra space. REORGANIZE only reorders leaf pages, compacting them; it’s online and less resource‑intensive. Expert strategy: reorganize for 5–30% fragmentation, rebuild above 30%.

Answer 38

Hints like HASH JOIN, FORCE ORDER, INDEX(...) override the optimizer. They are last‑resort tools for when you have better knowledge than the optimizer (e.g., large data skew). However, they hard‑code a plan that degrades over time as data changes. Always wrap with monitoring and document thoroughly. In interviews, stress that you try every declarative option first (indexes, stats, refactoring).

Answer 39

TempDB is used for sorts, hashes, spools, version store (snapshot isolation), and user temporary objects. It’s the single busiest database. Experts configure multiple data files (equal size, equal auto‑growth) to reduce allocation contention, separate it on fast storage, and monitor spill events to tune memory grants.

Answer 40

Step‑by‑step expert answer: “I start by capturing the actual execution plan with IO and time statistics. I look for the operator with the highest relative cost and biggest discrepancy between estimated and actual rows. In one case, a nested loop driving 8 million rows appeared. The cause was a missing foreign key index and a parameter sniffing issue. I created the index, added OPTION (RECOMPILE) temporarily, and the query went from 42 seconds to 0.8 seconds, reducing logical reads by 98%.”

Answer 41

Bitmap indexes (common in Oracle, PostgreSQL, data warehouses) map each distinct value to a bitmap of row ids. They’re extremely fast for low‑cardinality columns (gender, status) combined with AND/OR conditions. However, they cause massive locking overhead in OLTP because updating one row locks the entire bitmap segment. Use only in read‑heavy, bulk‑loaded environments.

Answer 42

CREATE INDEX ix_active ON Orders (OrderDate) WHERE Status = 'Active';

A filtered index contains only rows meeting a WHERE clause, reducing size and improving performance for that subset. Ideal for sparse columns, soft‑deletes, or active records.

Answer 43

Instead of indexing LastName, index UPPER(LastName). This makes WHERE UPPER(LastName) = 'SMITH' sargable. Known as “expression index” in PostgreSQL, “function‑based index” in Oracle. Expert trick: use for computed columns or DATE() wrappers that otherwise break index usage.

Answer 44

Columnstore stores data column‑wise, compressing heavily and enabling batch mode processing (thousands of rows at once). Ideal for large fact tables in data warehouses. SQL Server’s clustered columnstore can accelerate aggregate scans by 10‑100x. Trade‑off: slower single‑row lookups and higher CPU for compression.

Answer 45

Rowstore (traditional B‑tree) excels for point lookups and transactional workloads. Columnstore wins for scanning large volumes and aggregating. Expert architects often combine them: nonclustered columnstore on an OLTP table for analytics, or a columnstore table with rowstore secondary indexes for uniqueness enforcement.

Answer 46

Selectivity = (number of distinct values) / (total rows). High selectivity (>0.1) means an index is very useful; low selectivity (<0.01) often leads to scans. The best composite index places the most selective column first to prune the index tree aggressively.

Answer 47

Too many indexes degrade insert/update/delete performance because all indexes must be maintained. They also increase plan compilation overhead. Experts periodically identify unused indexes using DMVs (sys.dm_db_index_usage_stats) and consolidate overlapping indexes.

Answer 48

Execution plans show “Missing Index” hints with estimated improvement. The sys.dm_db_missing_index_* DMVs aggregate these suggestions over time. Blindly applying them leads to duplicate indexes; you must carefully merge with existing indexes, consider included columns, and validate impact with actual tests.

Answer 49

A robust strategy uses smart scripts like Ola Hallengren’s, with thresholds: Reorganize 5–30% fragmentation, rebuild >30%. Update statistics after rebuild. Schedule during maintenance windows, partition‑aware.

Answer 50

Order keys from the most selective to least selective for equality predicates, and then range predicates. For a query WHERE Status = 'Open' AND CreateDate >= '2025-01-01' ORDER BY Priority, the best index might be (Status, CreateDate, Priority).

Answer 51

When you see a Key Lookup in the plan, add the missing columns as included columns to turn the nonclustered index into a covering one. This can eliminate up to 95% of IO in frequent queries.

Answer 52

A heap (no clustered index) stores rows in no particular order. Range scans require full table scans. Forwarded records (due to row updates moving data) cause fragmented IO. While heaps can be faster for bulk inserts, any read‑heavy workload should have a well‑chosen clustered index.

Answer 53

The optimizer can seek multiple indexes on the same table and join the results (index intersection) or even use them for covering when a single index does not cover all columns. This is automatic but more expensive than a single well‑designed composite index. Aim to design indexes that avoid the need for intersection.

Answer 54

“Given a 200M‑row sales fact table and a query filtering by Region, ProductCategory, and grouping by Month, I’d create a composite nonclustered index on (Region, ProductCategory, SaleDate) with included columns for Amount. This provides seek on filters and covers the aggregation. Expected result: scan reduced from full table to 0.02% of pages, execution time from 5 minutes to 3 seconds.”

Answer 55

Diagnosis: The query did a clustered index scan of 2M rows for WHERE LastName LIKE 'Sm%' AND Country = 'US'. Original index on (Country) alone. Solution: Created index on (Country, LastName) covering also FirstName, Email. Plan switched to index seek plus key lookup removal. Result: Logical reads from 15,000 to 9, time 2.1s.

Answer 56

OLTP models are highly normalized (3NF/BCNF) to eliminate redundancy and speed up transactions. OLAP models use denormalized star/snowflake schemas optimized for bulk reads and aggregations. A senior architect knows the trade‑off: normalization protects data integrity; denormalization sacrifices some integrity for query performance.

Answer 57

A central fact table surrounded by dimension tables. Facts are numeric, additive measures; dimensions are descriptive. Joins are single‑level, making queries predictable and optimizer‑friendly. Perfect for business intelligence.

Answer 58

Dimensions are further split into sub‑dimensions (e.g., DimProduct → DimCategory → DimDepartment). This saves space and enforces hierarchy integrity but adds join complexity. Recommended only when dimension tables are large and storage savings justify the query overhead.

Answer 59

Data Vault splits data into Hubs (business keys), Links (relationships), and Satellites (descriptive attributes over time). It enables parallel loading, auditing, and schema evolution without breaking downstream models. An expert can discuss when it’s overkill vs. essential.

Answer 60

Type 0: retain original. Type 1: overwrite. Type 2: add new row with surrogate key, effective dates, current flag. Type 3: previous value column. Type 4: history table. Walk through Type 2 implementation using MERGE and derived start/end dates, emphasizing performance on billion‑row dimensions.

Answer 61

Surrogate keys are system‑generated integers (identity, sequence) that never change. Natural keys are business identifiers (email, SSN). Surrogates are smaller, faster for joins, and decouple from business changes. Always use surrogate keys as primary keys in dimensions, but maintain natural keys with unique constraints for lookup.

Answer 62

Natural keys are the business’s way of identifying entities. They’re essential for ETL deduplication, enforcing business uniqueness, and joining staging data to existing records. Often used as distribution key in MPP systems to co‑locate related facts and dimensions.

Answer 63

Transactional: one row per event. Periodic snapshot: aggregated state at regular intervals. Accumulating snapshot: tracks progress through a process, updated until final state. Choosing the right type depends on the business questions and query patterns.

Answer 64

Attributes like invoice number, transaction ID that don’t have a separate dimension because they are unique and have no descriptive attributes. Stored directly in the fact table, used for grouping and drill‑down.

Answer 65

When a fact can link to multiple dimensions (e.g., multiple sales reps per order), a bridge table captures the association. Querying requires joining through the bridge with a weight factor or DISTINCT/GROUP BY. Always use a bridge with allocation weight to avoid double‑counting.

Answer 66

Grain is the meaning of one row in a fact table (e.g., “one row per transaction line item”). Every dimension and fact must be consistent with this grain. Changing grain mid‑project leads to double‑counted metrics and confusion. “I start every data model discussion by agreeing on the grain with stakeholders, then design dimensions accordingly.”

Answer 67

Partition large tables by a high‑frequency predicate, typically date (month/day). Benefits: partition elimination for queries with the partitioning key, faster index maintenance per partition, and efficient sliding window archiving. Use partition‑aligned indexes.

Answer 68

Define a retention policy, then implement a process to move partitions to a history database or archive table with compression. For continuous archiving, use partition switching for instantaneous data movement. “We archive partitions older than 2 years to cheap storage, and queries against archived data use UNION ALL views with a date constraint.”

Answer 69

Retention policies are defined by legal, business, and storage cost requirements. They directly impact archiving design. Demonstrate awareness of GDPR “right to erasure” and how to implement soft deletes/hard deletes efficiently without breaking referential integrity.

Answer 70

Structured approach: “Interview stakeholders to identify business processes and grain. Choose a star schema with conformed dimensions. For incremental ETL, use Change Data Capture on source OLTP. Implement Type 2 SCD for customer and product dimensions. Partition facts by month. Create a columnstore index on the fact table and appropriate B‑tree indexes on dimensions. Finally, build an aggregation layer for top‑10 reports to reduce latency from 60s to 2s.”

Answer 71

Isolation levels define how transactions see each other’s changes: Read Uncommitted, Read Committed, Repeatable Read, Serializable. An expert understands the anomalies prevented (dirty, non‑repeatable, phantom) and the locking/latching implications.

Answer 72

A phantom read occurs when a transaction runs the same query twice and gets a different set of rows because another transaction inserted or deleted rows matching the predicate. Prevented by Serializable (range locks) or snapshot isolation.

Answer 73

Instead of locking rows, the engine maintains versions of modified rows in tempdb. A snapshot transaction sees a consistent point‑in‑time view. Eliminates writers blocking readers. However, increases tempdb load and can cause update conflicts.

Answer 74

RCSI provides statement‑level read consistency without holding shared locks. Reduces deadlocks drastically. First recommendation for high‑concurrency OLTP. Expert caveat: changes application behavior (readers see committed data, not the last data they updated) and requires monitoring tempdb version store growth.

Answer 75

To save memory, SQL Server escalates many fine‑grain locks to a single table lock, blocking other sessions. Mitigation: LOCK_ESCALATION = DISABLE on partitioned tables, snapshot isolation to reduce lock count, and batching large updates.

Answer 76

A deadlock graph shows two processes waiting on each other’s locks. The victim is automatically chosen and terminated. Analysis identifies the resources and lock modes. Common pattern: key lookup causing shared lock on clustered index while update holds exclusive lock. Solution: covering index to avoid the lookup.

Answer 77

Deadlocks and timeouts should be caught, and the transaction retried with exponential backoff. The retry block must be idempotent – re‑issue the entire business operation. In cloud‑distributed systems, implement at the data access layer using libraries like Polly.

Answer 78

Write skew happens under snapshot isolation when two concurrent transactions read the same data, then make decisions based on that read and both update different rows, violating a constraint. The database can’t detect this because no row conflict occurs. Solution: promote to serializable or use materialized conflict checks.

Answer 79

Every change is first logged in the transaction log before modifying data pages. Ensures durability and rollback capability. Expert knowledge: VLF fragmentation, log growth management, and how bulk‑logged recovery model reduces logging for bulk operations.

Answer 80

A checkpoint writes all dirty data pages from memory to disk, creating a known good point for crash recovery. Indirect checkpoints (SQL Server) base frequency on target recovery time, smoothing IO. Tuning checkpoints is crucial for consistent performance.

Answer 81

On restart, the database reads the log: Redo rolls forward all committed transactions not yet on disk. Undo rolls back uncommitted transactions using compensation log records. Understanding this ensures you design for fast recovery by keeping long‑running transactions short and managing log size.

Answer 82

When updating two separate databases, a transaction manager coordinates: prepare phase (each resource manager votes commit/abort) and commit phase. Guarantees atomicity across systems but introduces latency and potential blocking if the coordinator fails. Mention XA and modern alternatives like Sagas.

Answer 83

In distributed NoSQL systems, updates propagate asynchronously, leading to a window where different nodes return stale data. Acceptable for many big data applications. A senior SQL professional might discuss handling eventual consistency in a hybrid architecture using version vectors or last‑write‑wins.

Answer 84

“I’d use Serializable isolation for funds transfer to prevent phantoms and double‑spending. The transaction locks both account rows in a consistent order (by AccountID) to prevent deadlocks. For high throughput, consider optimistic concurrency with row versioning and validate balance before commit, with retry logic. Result: zero consistency errors with 10,000 transfers/second.”

Answer 85

“By setting the database to Read Committed Snapshot Isolation, all reads become statement‑level consistent without locking, eliminating dirty reads. In critical paths where I need explicit control, I’d use SET TRANSACTION ISOLATION LEVEL READ COMMITTED within the stored procedure and validate that no uncommitted data could be accessed.”

Answer 86

WITH Marked AS (
    SELECT ID, Value, TS,
           LAG(TS) OVER (ORDER BY TS) AS PrevTS
    FROM SensorData
)
SELECT PrevTS AS GapStart, TS AS GapEnd,
       DATEDIFF(SECOND, PrevTS, TS) AS GapSeconds
FROM Marked WHERE DATEDIFF(SECOND, PrevTS, TS) > expected_interval;

Impact: Detected missing sensor reads in real‑time, allowing alerting within seconds.

Answer 87

WITH Boundaries AS (
    SELECT *, CASE WHEN DATEDIFF(MINUTE, LAG(EventTime) OVER (PARTITION BY UserID ORDER BY EventTime), EventTime) > 30 THEN 1 ELSE 0 END AS NewSession
    FROM UserEvents
), Sessions AS (
    SELECT *, SUM(NewSession) OVER (PARTITION BY UserID ORDER BY EventTime) AS SessionID
    FROM Boundaries
)
SELECT UserID, SessionID, MIN(EventTime) StartTime, MAX(EventTime) EndTime, COUNT(*) Actions
FROM Sessions GROUP BY UserID, SessionID;

Performance: Replaced Python script, processing 100M events in under 30 seconds.

Answer 88

MERGE DimCustomer AS t
USING StagingCustomer AS s ON t.BusinessKey = s.BusinessKey AND t.IsCurrent = 1
WHEN MATCHED AND t.Name != s.Name THEN
    UPDATE SET IsCurrent = 0, EndDate = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (BusinessKey, Name, StartDate, IsCurrent)
    VALUES (s.BusinessKey, s.Name, GETDATE(), 1);

Always wrap MERGE in a transaction and discuss known concurrency bugs.

Answer 89

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY arrival_time DESC) rn
    FROM stream_batch
)
SELECT * FROM cte WHERE rn = 1;

Pair with a unique index on business_key for persistence.

Answer 90

CDC captures insert/update/delete operations from the transaction log and exposes them via system functions. Provides net change for each row, drastically reducing volume compared to full snapshots. Expert implements CDC with partitioning, retention cleanup jobs, and LSN‑based watermarks.

Answer 91

System‑versioned temporal tables automatically maintain a history table with valid‑from/to periods. Queries can FOR SYSTEM_TIME AS OF for point‑in‑time analysis. Preferred over manual triggers for performance and data integrity, as it’s maintained by the engine with minimal overhead.

Answer 92

Dynamic data masking (DDM) hides data on‑the‑fly based on user permissions without altering stored values. Combine with Always Encrypted for high security. Allows developers to work with realistic data volumes without seeing PII.

Answer 93

Capture actual execution plan + wait stats.
Identify dominant operator and estimated vs actual row discrepancy.
Check statistics age and update if needed.
Evaluate missing index DMVs and design covering index.
Refactor non‑sargable predicates.
Test with realistic data, measure IO and time.

Answer 94

Partition by date.
Columnstore index for aggregate queries.
B‑tree indexes only on frequently filtered columns with high selectivity.
Incremental ETL via partition switching.
Use page/row compression.
Query with partition elimination.
Materialize common aggregations via indexed views or summary tables.

Answer 95

“I maintain a LastProcessedTimestamp table, read source rows where ModifiedDate > watermark, transform and load, then update the watermark. For late‑arriving facts, I use a 3‑day lookback window and upsert logic. Wrapped in transaction to avoid duplicate loads.”

Answer 96

Disable nonclustered indexes and foreign keys before insert.
Use TABLOCK hint for minimal logging in bulk‑logged recovery.
Insert in batches to avoid transaction log blow‑up.
Pre‑sort data to match clustered index.
Rebuild indexes and update statistics after load.

Answer 97

Scenario: Counting actions per user where a few users have billions of actions. Solution: Pre‑aggregate in stages: first aggregate by user and day, then sum. Or use APPROX_COUNT_DISTINCT. In distributed systems, two‑phase aggregation smooths skew.

Answer 98

Shared database, separate schema: easy management, but no tenant‑level restore. Shared schema with TenantID everywhere: most efficient, requires row‑level security. Isolated database per tenant: highest isolation, difficult to manage at scale. “I typically advocate shared schema + RLS and encrypted columns for PII, with elastic pools for cost control.”

Answer 99

“For SQL Server, I’d configure a multi‑subnet Always On Availability Group with synchronous commit for the primary replica, automatic failover, and asynchronous readable secondaries for reporting. Combine with regular backups and geo‑replication for disaster recovery. Monitoring: failover time < 5 seconds, RPO < 1 minute.”

Answer 100

“I’d start by designing a star schema with scalable surrogate keys. ETL uses CDC and watermarks, loading into a partitioned fact table with clustered columnstore index. Security via RLS and data masking. Queries tuned with covering indexes and indexed views; caching reports with Redis. Monitoring: Extended Events for long‑running queries, deadlock graphs, and resource consumption, feeding into Grafana dashboards. Result: 100x faster analytics, zero data loss, 99.999% availability, fully GDPR compliant.”

Answer 101

Always use sp_executesql instead of EXEC() because it allows parameterized queries, reducing SQL injection risk and promoting plan reuse.

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Sales WHERE Region = @region';
EXEC sp_executesql @sql, N'@region NVARCHAR(50)', @region = 'EMEA';

This avoids concatenation and ensures a single plan for different region values.

Answer 102

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Category), ',') FROM (SELECT DISTINCT Category FROM Products) t;
SET @query = 'SELECT * FROM (SELECT Product, Category, Sales FROM SalesData) src
PIVOT (SUM(Sales) FOR Category IN (' + @cols + ')) p;';
EXEC sp_executesql @query;

Answer 103

-- Shredding JSON array into rows
SELECT * FROM OPENJSON(@json) WITH (Name NVARCHAR(50), Age INT);
-- Generating JSON output
SELECT CustomerID, Name FOR JSON PATH;

Use JSON functions instead of string parsing for maintainable and optimised code.

Answer 104

SELECT 
    x.value('(Name/text())[1]', 'NVARCHAR(50)') AS Product,
    x.value('(Price/text())[1]', 'DECIMAL(10,2)') AS Price
FROM @xml.nodes('/Catalog/Product') AS t(x);

Answer 105

BEGIN TRY
    BEGIN TRANSACTION;
    -- DML operations
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW; -- re‑raise original error
END CATCH;

Always check @@TRANCOUNT before rolling back. Use THROW instead of RAISERROR for cleaner error propagation.

Answer 106

SET XACT_ABORT ON ensures that any runtime error automatically rolls back the entire transaction and aborts the batch. This is critical for data consistency in high‑volume systems. It also improves performance by reducing log flushes.

Answer 107

BEGIN TRANSACTION;
SAVE TRANSACTION BeforeRiskyUpdate;
-- risky operation
IF @@ERROR <> 0 ROLLBACK TRANSACTION BeforeRiskyUpdate;
COMMIT;

Use savepoints sparingly; they add complexity and can hide logic errors.

Answer 108

CREATE PROCEDURE dbo.usp_InsertOrder
    @OrderID INT, @Amount DECIMAL
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    INSERT INTO dbo.Orders_InMem (OrderID, Amount) VALUES (@OrderID, @Amount);
END;

Natively compiled procs can be 10‑30x faster for OLTP workloads by eliminating interpretation overhead.

Answer 109

-- Table definition with SYSTEM_VERSIONING
CREATE TABLE dbo.Employee (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

-- Point-in-time query
SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2025-01-01';

Answer 110

SELECT Person.name, Restaurant.name
FROM Person, likes, Restaurant
WHERE MATCH(Person-(likes)->Restaurant);

Graph tables simplify complex many‑to‑many relationship queries like friend‑of‑friend analysis.

Answer 111

SELECT value FROM STRING_SPLIT('apple,banana,grape', ',');

Available from SQL Server 2016 onward; much faster than custom CLR or XML‑based parsing.

Answer 112

SELECT * FROM Sales PIVOT (SUM(Amount) FOR Month IN ([Jan], [Feb], [Mar])) p;

Use UNPIVOT to normalize columns back to rows.

Answer 113

MERGE can perform insert/update/delete in one statement, but it has known concurrency bugs. Always use with HOLDLOCK to prevent race conditions. Prefer separate statements for critical OLTP paths.

Answer 114

CREATE TYPE dbo.IDList AS TABLE (ID INT);
CREATE PROC dbo.usp_ProcessIDs @IDs IDList READONLY AS
SELECT * FROM Orders WHERE CustomerID IN (SELECT ID FROM @IDs);

More efficient than comma‑delimited strings and avoids dynamic SQL.

Answer 115

CREATE TYPE dbo.IDList_mem AS TABLE (ID INT PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON);

Greatly reduces tempdb usage and speeds up parameter passing for high‑frequency calls.

Answer 116

FORMAT() uses .NET runtime and is slower than CONVERT() with style codes. Use CONVERT in set‑based queries for better performance.

Answer 117

ISNULL returns the data type of the first argument; COALESCE follows data type precedence. COALESCE can evaluate multiple expressions; it’s ANSI standard. For simplicity, prefer COALESCE.

Answer 118

SELECT APPROX_COUNT_DISTINCT(UserID) FROM UserEvents;

Provides an approximate count (with ~2% error) at a fraction of the time and memory of a precise count. Ideal for real‑time dashboards on huge datasets.

Answer 119

SELECT j.Customer, o.OrderID
FROM OPENJSON(@json) WITH (Customer NVARCHAR(50), Orders NVARCHAR(MAX) AS JSON) AS j
CROSS APPLY OPENJSON(j.Orders) WITH (OrderID INT) o;

Answer 120

WHERE (@Name IS NULL OR Name = @Name)
OPTION (RECOMPILE);

The OPTION (RECOMPILE) trick allows the optimizer to produce a tailored plan for each parameter combination, eliminating parameter sniffing problems on search forms.

Answer 121

Goal: Implement SCD Type‑2 for DimProduct using T‑SQL. Steps:

Create staging table with new data.
Use MERGE to expire old rows and insert new versions.
Maintain EffectiveStartDate, EffectiveEndDate, and IsCurrent flag.
Test with incremental loads.

-- Sample MERGE for Type 2
MERGE DimProduct AS target
USING StagingProduct AS source ON target.BusinessKey = source.BusinessKey AND target.IsCurrent = 1
WHEN MATCHED AND (target.Name != source.Name OR target.Price != source.Price) THEN
    UPDATE SET IsCurrent = 0, EndDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (BusinessKey, Name, Price, StartDate, IsCurrent)
    VALUES (source.BusinessKey, source.Name, source.Price, GETDATE(), 1);

Lab outcome: Successfully tracked all historical changes, queryable by date range.

Answer 122

Steps: Enable CDC on source table → Read changes using cdc.fn_cdc_get_all_changes_... → Apply net changes to staging → Merge into target data warehouse. Use LSN‑based watermarks to ensure exactly‑once delivery.

Answer 123

Simulate a stored procedure that runs fast for one parameter but slow for another. Capture the actual plan and compare estimated vs actual rows. Apply OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN and document performance improvement (e.g., from 8s to 0.2s).

Answer 124

Create a partition function on Date, align a staging table with the same structure, then switch the oldest partition out to archive. Demonstrate zero‑downtime data movement and verify row counts.

Answer 125

Enable Query Store, identify a regressed query, view multiple plans, and force the high‑performance plan. Monitor to ensure stability. Discuss scenarios where automatic plan correction can help.

Answer 126

Using the SUM() of boundary flag trick, group user events into sessions. Optimize with covering indexes on UserID, EventTime. Measure execution time on 100M+ rows.

Answer 127

CREATE FUNCTION dbo.fn_tenant_security(@TenantID INT) RETURNS TABLE
WITH SCHEMABINDING AS
RETURN SELECT 1 AS result WHERE @TenantID = CONVERT(INT, SESSION_CONTEXT(N'TenantID'));

Create security policy to filter rows automatically. Test with different tenant contexts.

Answer 128

Walk through actual plan, add missing indexes, introduce pre‑aggregated indexed view, replace scalar UDF with inline logic. Document IO reduction and plan changes.

Answer 129

Encrypt a sensitive column (e.g., SSN) with randomized encryption, demonstrate that even DBAs can’t see plaintext. Use PowerShell to provision keys and test equality lookups with deterministic encryption.

Answer 130

Create a memory‑optimized staging table, insert batches with ROW_NUMBER() dedup, then merge into durable disk‑based table. Measure latency < 10ms per batch.

Answer 131

Add masking functions to PII columns, then query as privileged user vs masked user. Verify masking in execution plan.

Answer 132

SELECT TOP 10 total_worker_time/execution_count AS avg_cpu, text
FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avg_cpu DESC;

Answer 133

Create an Extended Events session for xml_deadlock_report, analyze graph in SSMS, identify root cause.

Answer 134

Configure IndexOptimize with thresholds and time limits, schedule job, verify fragmentation reduction.

Answer 135

WITH BOM AS (
    SELECT ProductID, ComponentID, 1 AS Level FROM BillOfMaterials WHERE ProductID = @TopProduct
    UNION ALL
    SELECT b.ProductID, b.ComponentID, Level + 1 FROM BillOfMaterials b JOIN BOM ON b.ProductID = BOM.ComponentID
) SELECT * FROM BOM;

Answer 136

Create new partition for next month, switch out oldest to archive, merge empty partition. Automate with SQL Agent.

Answer 137

Create external data source, external table, and join with local DW. Demonstrate predicate pushdown.

Answer 138

Capture the bad plan, create a plan guide forcing a desired plan shape using sp_create_plan_guide.

Answer 139

Create identical data, run SUM, GROUP BY queries; document 10‑50x speedup with columnstore.

Answer 140

Enable automatic tuning, let system create/drop indexes, monitor improvements and rollback ability.

Answer 141

WITH groups AS (
    SELECT *, COUNT(Value) OVER (ORDER BY TS) AS grp
    FROM SensorReadings
)
SELECT TS, FIRST_VALUE(Value) OVER (PARTITION BY grp ORDER BY TS) AS FilledValue
FROM groups;

Answer 142

Union tables with CHECK constraints, enabling optimizer to eliminate irrelevant tables at query time.

Answer 143

Nodes: Person, edges: Follows. Query with MATCH to find friends of friends not directly followed.

Answer 144

Create full‑text catalog, index on product description, run ranked queries with CONTAINSTABLE.

Answer 145

Simulate two sessions updating tables in opposite order, capture deadlock graph, fix by enforcing consistent access order.

Answer 146

Add IsDeleted flag, create filtered indexes, ensure all queries respect the flag, and build a scheduled hard‑delete job for data past retention.

Answer 147

Apply COLUMNSTORE_ARCHIVE compression on older partitions to save storage (up to 10x additional compression).

Answer 148

Use CDC to push changes to Event Hubs, then process with Azure Stream Analytics, output alerts.

Answer 149

Use sys.dm_db_stats_properties to find stats with high modification count, update only those exceeding 10% changes.

Answer 150

Configure publication, mobile clients sync data, handle conflicts with custom resolver.

Answer 151

Diagnosed a plan with a scalar UDF called per row. Converted UDF to inline table‑valued function and applied CROSS APPLY. Result: report time from 2 hours to 45 seconds.

Answer 152

SELECT *, SUM(Amount) OVER (PARTITION BY AccountID ORDER BY Date ROWS UNBOUNDED PRECEDING) 
- COALESCE(SUM(CASE WHEN ResetFlag=1 THEN Amount ELSE 0 END) OVER (PARTITION BY AccountID ORDER BY Date ROWS UNBOUNDED PRECEDING),0) AS RunningTotal;

Answer 153

SELECT a.*, b.* FROM Appointments a
INNER JOIN Appointments b ON a.RoomID = b.RoomID AND a.Start < b.End AND a.End > b.Start
WHERE a.ID < b.ID;

Answer 154

Combine dynamic SQL pivot with ROLLUP or GROUPING SETS to add subtotals and grand total.

Answer 155

SELECT c.Date, ISNULL(s.Sales, 0) AS Sales
FROM Calendar c LEFT JOIN Sales s ON c.Date = s.SaleDate
WHERE c.Date BETWEEN @Start AND @End;

Answer 156

WITH ranked AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC) dr
    FROM Employee
) SELECT * FROM ranked WHERE dr <= 3;

Answer 157

WITH grp AS (
    SELECT UserID, LoginDate,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginDate), LoginDate) AS grp
    FROM Logins
)
SELECT UserID, MIN(LoginDate) Start, MAX(LoginDate) End, COUNT(*) Streak
FROM grp GROUP BY UserID, grp HAVING COUNT(*) >= 7;

Answer 158

DELETE TOP(1) FROM QueueTable WITH (READPAST, ROWLOCK)
OUTPUT deleted.*; -- process the deleted row

Allows multiple consumers to process without blocking each other.

Answer 159

SELECT o.* FROM Orders o LEFT JOIN Customers c ON o.CustomerID = c.ID
WHERE c.ID IS NULL;

Answer 160

SELECT * FROM SourceTable EXCEPT SELECT * FROM TargetTable;
-- Also check the reverse to find missing/extra rows.

Answer 161

WITH dates AS (
    SELECT CAST('2025-01-01' AS DATE) dt
    UNION ALL SELECT DATEADD(DAY,1,dt) FROM dates WHERE dt < '2025-12-31'
) SELECT * FROM dates OPTION (MAXRECURSION 0);

Answer 162

SELECT Split.a.value('.', 'VARCHAR(100)') AS Value
FROM (SELECT CAST('' + REPLACE(List, ',', '') + '' AS XML) AS Data) AS A
CROSS APPLY Data.nodes('/M') AS Split(a);

Answer 163

SELECT Sales, PERCENT_RANK() OVER (ORDER BY Sales) AS Percentile FROM Orders;

Answer 164

SELECT t.*, r.Rate
FROM Transactions t
JOIN Rates r ON t.Currency = r.Currency AND t.TradeDate BETWEEN r.StartDate AND r.EndDate;

Answer 165

Use dynamic SQL with sp_executesql to build only necessary predicates, avoiding OR bombs. Or apply the OPTION (RECOMPILE) trick for simpler code.

Answer 166

CREATE UNIQUE INDEX IX_Vote ON Votes (UserID, PollID);
-- INSERT with TRY...CATCH to handle duplicate gracefully.

Answer 167

AVG(Value) OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MA7;

Answer 168

SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Value) OVER () AS Median FROM Data;

Answer 169

SELECT 'SourceOnly', * FROM (SELECT * FROM Source EXCEPT SELECT * FROM Target) s
UNION ALL
SELECT 'TargetOnly', * FROM (SELECT * FROM Target EXCEPT SELECT * FROM Source) t;

Answer 170

WHILE 1=1 BEGIN
    DELETE TOP (5000) FROM Orders WHERE Status = 'Archived';
    IF @@ROWCOUNT = 0 BREAK;
    WAITFOR DELAY '00:00:01'; -- throttle
END;

Answer 171

Join a calendar table with business day flag; count rows where flag = 1.

Answer 172

WITH RCTE AS (SELECT ID, Name, CAST(Name AS NVARCHAR(MAX)) Path FROM Folders WHERE ParentID IS NULL
UNION ALL SELECT f.ID, f.Name, r.Path + '/' + f.Name FROM Folders f JOIN RCTE r ON f.ParentID = r.ID)
SELECT * FROM RCTE;

Answer 173

SELECT * FROM Products ORDER BY ProductID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

More readable; performance similar when supported (SQL 2012+).

Answer 174

SELECT Region, Product, SUM(Sales) FROM SalesData GROUP BY GROUPING SETS ((Region, Product), (Region), ());

Answer 175

Combine recursive CTE with OPENJSON to traverse unknown depth.

Answer 176

UPDATE Product SET Price=@newPrice, RowVer=@newRowVer WHERE ID=@id AND RowVer=@oldRowVer;
IF @@ROWCOUNT=0 THROW 50000, 'Concurrency conflict', 1;

Answer 177

SELECT ID+1 AS gap_start, nextID-1 AS gap_end FROM (SELECT ID, LEAD(ID) OVER (ORDER BY ID) nextID FROM Table) t WHERE nextID - ID > 1;

Answer 178

CREATE SECURITY POLICY rls_policy ADD FILTER PREDICATE dbo.fn_access(@AppUserID) ON dbo.SensitiveData,
ADD BLOCK PREDICATE dbo.fn_access(@AppUserID) ON dbo.SensitiveData;

Answer 179

Run sp_whoisactive @get_plans = 1, @get_locks = 1 to see blocking chains, current plans, and wait info. Essential for on‑call DBAs.

Answer 180

Automate index rebuild, stats update, and integrity checks across hundreds of instances using Invoke-DbaQuery and Optimize-DbaIndex.

Answer 181

Query Store captures execution plans and runtime stats. It helps identify regressions by comparing plan performance over time. You can force a known‑good plan using sp_query_store_force_plan. This is a game‑changer for plan stability after upgrades.

Answer 182

Interleaved execution (SQL Server 2017+) allows multi‑statement table‑valued functions to get accurate cardinality estimates by pausing the main query and executing the function first. This avoids huge misestimates from MSTVFs.

Answer 183

If a query spills to tempdb due to insufficient memory grant, the engine can adjust the grant on subsequent executions. This self‑tuning feature reduces out‑of‑memory errors and improves concurrency.

Answer 184

Functions like APPROX_COUNT_DISTINCT and APPROX_PERCENTILE use HyperLogLog and other sketches to give near‑instant results on huge datasets. Ideal for dashboards; not for precise financial reports.

Answer 185

In SQL Server 2019+, scalar UDFs can be automatically inlined into the query plan, eliminating the per‑row overhead. This dramatically improves performance of queries that used to call scalar UDFs in SELECT.

Answer 186

Columnstore indexes suffer from segment fragmentation and deleted rows. Use REORGANIZE with COMPRESS_ALL_ROW_GROUPS option to compact and remove deleted rows. Full rebuild only when necessary.

Answer 187

When you combine partitioning with columnstore, align nonclustered indexes on the same partition scheme. Queries filtering on the partition key will skip entire partitions, giving huge IO savings.

Answer 188

SELECT wait_type, wait_time_ms, signal_wait_time_ms FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' ORDER BY wait_time_ms DESC;

Focus on PAGEIOLATCH (IO), WRITELOG (log flush), CXPACKET (parallelism), LCK_* (blocking).

Answer 189

SELECT TOP 10 total_worker_time/execution_count AS avg_cpu, text
FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avg_cpu DESC;

Answer 190

Configure multiple tempdb data files (one per core, up to 8). Enable trace flag 1118 for uniform extent allocation. Monitor sys.dm_os_wait_stats for PAGELATCH_* waits on tempdb.

Answer 191

Enable instant file initialization (IFI) by granting the SQL Server service account “Perform Volume Maintenance Tasks” privilege. This skips zeroing new data file allocations, making file growths nearly instantaneous.

Answer 192

Durable memory‑optimized tables log changes to the transaction log and continuously checkpoint data and delta files. This provides full ACID durability with microsecond latency. Essential for high‑speed trading and IoT ingestion.

Answer 193

CREATE EXTERNAL DATA SOURCE HadoopDS WITH (LOCATION = 'hdfs://...');
CREATE EXTERNAL TABLE dbo.ExternalSales ( ... ) WITH (LOCATION = '/sales/', DATA_SOURCE = HadoopDS);
-- Then join with local tables.

Answer 194

Configure readable secondary replicas to offload reporting and backups. Use ApplicationIntent=ReadOnly in connection string to route read queries. Monitor synchronization latency and failover readiness.

Answer 195

Azure SQL can automatically force the last known good plan when a regression is detected. It can also create or drop indexes based on missing index DMV analysis. This reduces manual DBA effort.

Answer 196

SELECT query_plan_hash, COUNT(*) FROM sys.dm_exec_query_stats GROUP BY query_plan_hash
HAVING COUNT(*) > 1; -- Find queries with multiple plans.

Answer 197

Use sp_estimate_data_compression_savings to predict space savings. PAGE compression includes prefix and dictionary compression; suitable for data warehouses. ROW compression reduces metadata overhead for OLTP.

Answer 198

Classify incoming connections and assign them to resource pools with CPU, memory, and IO limits. Protects the server from runaway reports or ETL jobs.

Answer 199

DELAYED_DURABILITY = FORCED (or per transaction) reduces log flush frequency, boosting insert throughput at the risk of losing committed transactions on crash. Suitable for non‑critical logging scenarios.

Answer 200

Baseline: capture wait stats, top queries, current IO.
Identify: use Query Store/plan cache to find regressions.
Analyze: actual execution plans, statistics, missing indexes.
Implement: add/remove indexes, rewrite queries, adjust settings.
Validate: compare before/after metrics (time, IO, CPU).
Monitor: set up alerts for plan changes and heavy queries.

Answer 201

Advanced troubleshooting: DBCC PAGE (dbid, fileid, pageid, 3) with trace flag 3604 to see raw page contents, useful for understanding fragmentation and row structure.

Answer 202

Use FOR JSON PATH to shape output as nested JSON graphs, directly serving as GraphQL responses without additional middleware.

Answer 203

SELECT SalesDateTime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC' FROM Sales;

FreeLearning365

Ultimate Advanced SQL Interview Mastery Guide – 200+ Expert-Level Questions, Detailed Answers & Performance Secrets | FreeLearning365

🚀 The Ultimate Advanced SQL Interview Mastery Guide

🔹 1. Advanced Query Logic (1–20)

🔹 2. Execution Plans & Optimization (21–40)

🔹 3. Advanced Indexing Strategy (41–55)

🔹 4. Data Modeling & Architecture (56–70)

🔹 5. Transactions, Concurrency & Recovery (71–85)

🔹 6. Advanced Scenarios & Problem Solving (86–100)

🔹 7. Advanced T-SQL Techniques & Programming (101–120)

🔹 8. Hands‑on Labs & Real‑World Implementation (121–150)

🔹 9. Advanced Problem Solving & Case Studies (151–180)

🔹 10. Performance Engineering & Cloud‑Scale SQL (181–200+)

Post a Comment

0 Comments

Contact Us

Popular Posts

The Complete ERP Mastery Series: From Basics to SAP S/4HANA Expert | FreeLearning365

English for Bangladeshis: The Deshi Dose – The Ultimate Course Outline

English Alchemy: Transform Your Grammar from Confusion to Confidence (A Bangladesh-Centric Guide) - Part1

Mobile App

Categories

Free Learning 365

Random Posts

Social Media

Recent in Courses

Popular Posts

The Complete ERP Mastery Series: From Basics to SAP S/4HANA Expert | FreeLearning365

English for Bangladeshis: The Deshi Dose – The Ultimate Course Outline

English Alchemy: Transform Your Grammar from Confusion to Confidence (A Bangladesh-Centric Guide) - Part1

Menu Footer Widget

FreeLearning365

Ultimate Advanced SQL Interview Mastery Guide – 200+ Expert-Level Questions, Detailed Answers & Performance Secrets | FreeLearning365

🔹 1. Advanced Query Logic (1–20)

🔹 2. Execution Plans & Optimization (21–40)

🔹 3. Advanced Indexing Strategy (41–55)

🔹 4. Data Modeling & Architecture (56–70)

🔹 5. Transactions, Concurrency & Recovery (71–85)

🔹 6. Advanced Scenarios & Problem Solving (86–100)

🔹 7. Advanced T-SQL Techniques & Programming (101–120)

🔹 8. Hands‑on Labs & Real‑World Implementation (121–150)

🔹 9. Advanced Problem Solving & Case Studies (151–180)

🔹 10. Performance Engineering & Cloud‑Scale SQL (181–200+)

Post a Comment

0 Comments

Contact Us

Social Plugin

Popular Posts

The Complete ERP Mastery Series: From Basics to SAP S/4HANA Expert | FreeLearning365

English for Bangladeshis: The Deshi Dose – The Ultimate Course Outline

English Alchemy: Transform Your Grammar from Confusion to Confidence (A Bangladesh-Centric Guide) - Part1

Mobile App

Categories

Free Learning 365

Random Posts

Social Media

Recent in Courses

Popular Posts

The Complete ERP Mastery Series: From Basics to SAP S/4HANA Expert | FreeLearning365

English for Bangladeshis: The Deshi Dose – The Ultimate Course Outline

English Alchemy: Transform Your Grammar from Confusion to Confidence (A Bangladesh-Centric Guide) - Part1

Menu Footer Widget