Advanced SQL & Query Optimization – 60 MCQs (Answers & Explanations

Inline)
Q1: Which SQL keyword is most useful for eliminating duplicate rows in query results?

 A) GROUP BY
 B) DISTINCT
 C) UNIQUE
 D) HAVING

Correct Answer: B

Explanation: DISTINCT removes duplicate rows; GROUP BY aggregates rows.

Q2: What is the main purpose of using a CTE (Common Table Expression)?

 A) Create temporary tables on disk

 B) Improve query readability and recursion
 C) Force optimizer to use indexes
 D) Store results permanently

Correct Answer: B

Explanation: CTEs improve readability and support recursion; they are not persisted.

Q3: Which operator is faster for existence checks in SQL Server?

 A) IN
 B) EXISTS
 C) JOIN
 D) UNION

Correct Answer: B

Explanation: EXISTS short-circuits on the first match and handles NULLs well.

Q4: Which SQL Server feature stores multiple query execution plans and lets you force a
stable one?

 A) Query Store
 B) Plan Cache
 C) Extended Events
 D) Activity Monitor

Correct Answer: A

Explanation: Query Store captures multiple plans and can force a good plan.

Q5: What does the SQL Server OPTION (RECOMPILE) hint do?

 A) Runs without indexes

 B) Creates a new plan every time
 C) Prevents stats updates
 D) Fixes parameter sniffing without recompiles

Correct Answer: B

Explanation: OPTION (RECOMPILE) discards the cached plan and builds a fresh one per
execution.

Q6: Which join is typically most expensive when used unnecessarily?

 A) INNER JOIN
 B) CROSS JOIN
 C) LEFT JOIN
 D) RIGHT JOIN

Correct Answer: B

Explanation: CROSS JOIN creates a Cartesian product, exploding row counts.

Q7: Which function is most useful in window functions for running totals?

 A) ROW_NUMBER()
 B) RANK()
 C) SUM() OVER()
 D) NTILE()

Correct Answer: C

Explanation: SUM() OVER(ORDER BY ...) computes cumulative totals without grouping.

Q8: Which clause is applied after aggregation in logical processing?

 A) WHERE
 B) HAVING
 C) GROUP BY
 D) ORDER BY

Correct Answer: B

Explanation: HAVING filters groups after aggregation; WHERE filters rows before grouping.

Q9: Which indexing strategy best speeds up WHERE Col1 LIKE 'abc%' queries?

 A) Full-text index
 B) Nonclustered index on Col1
 C) Clustered index on Col1
 D) Filtered index

Correct Answer: B

Explanation: Prefix LIKE predicates can use a nonclustered index; full-text is for linguistic
search patterns.

Q10: What is parameter sniffing in SQL Server?

 A) Security issue with parameters

 B) Optimizer reusing a plan based on first parameter value
 C) Error when parameters missing
 D) Index fragmentation due to parameters

Correct Answer: B

Explanation: The optimizer compiles using initial parameter values and may reuse a
suboptimal plan later.

Q11: Effect of WITH (NOLOCK) table hint?

 A) Ignores indexes
 B) Reads uncommitted data and avoids locks
 C) Forces clustered scan
 D) Disables plan reuse

Correct Answer: B

Explanation: NOLOCK allows dirty reads by not acquiring shared locks.

Q12: Which execution operator indicates a costly full access?

 A) Hash Match
 B) Index Seek
 C) Table Scan
 D) Nested Loop

Correct Answer: C

Explanation: A Table Scan reads the whole table and often signals a missing index.

Q13: Which optimization technique helps when multiple queries reuse the same expensive
subquery?

 A) Use HAVING
 B) Use Views or CTEs
 C) Use CROSS JOIN
 D) Use Triggers

Correct Answer: B

Explanation: Views/CTEs centralize logic and can avoid repeating work.

Q14: Which DMV/metadata view highlights optimizer-suggested missing indexes?

 A) sys.dm_exec_query_stats
 B) sys.dm_db_missing_index_details
 C) sys.dm_tran_locks
 D) sp_helpindex

Correct Answer: B

Explanation: dm_db_missing_index_details lists missing index recommendations.

Q15: What does SET STATISTICS IO ON provide?

 A) Row counts only

 B) Logical/physical I/O stats
 C) Execution time only
 D) Actual plan
Correct Answer: B

Explanation: It prints logical/physical reads per table for the statement.

Q16: What is the main purpose of query hints?

 A) Automatically create indexes

 B) Influence the optimizer’s decisions
 C) Disable execution plans
 D) Persist query outputs

Correct Answer: B

Explanation: Hints like HASH JOIN / MERGE JOIN / FORCESEEK override optimizer choices.

Q17: Which improves performance on large fact tables?

 A) Heap tables
 B) Columnstore indexes
 C) Only rowstore indexes
 D) Triggers

Correct Answer: B

Explanation: Columnstore indexes enable batch mode and high compression for analytics.

Q18: Which query pattern benefits most from a covering index?

 A) Complex multi-table joins

 B) Selecting only indexed columns
 C) CROSS APPLY queries
 D) Large updates

Correct Answer: B

Explanation: If all needed columns are in the index (keys+INCLUDE), lookups are
eliminated.

Q19: Function of the Key Lookup operator?

 A) Reads from heap

 B) Fetches missing columns from clustered index
 C) Returns unique keys
 D) Removes duplicates

Correct Answer: B

Explanation: Nonclustered index provides keys; lookup retrieves remaining columns from
the base (clustered) index.

Q20: Which operator repeated often can signal a missing covering index?

 A) Nested Loop Join

 B) Index Seek
 C) Hash Match
 D) Key Lookup

Correct Answer: D

Explanation: Frequent lookups indicate not all needed columns are covered by the
nonclustered index.

Q21: Effect of OPTIMIZE FOR UNKNOWN?

 A) Avoids parameter sniffing issues

 B) Forces scans
 C) Creates filtered stats
 D) Enables parallelism

Correct Answer: A

Explanation: The optimizer uses average density instead of sniffed parameter values.

Q22: What allows reuse of precompiled plans?

 A) Temporary tables
 B) Plan cache
 C) Table variables
 D) Indexed views

Correct Answer: B

Explanation: The plan cache stores compiled plans to reduce CPU for recompilation.
Q23: Which DMV exposes execution times/CPU for cached queries?

 A) sys.dm_exec_sessions
 B) sys.dm_exec_query_stats
 C) sys.dm_db_partition_stats
 D) sys.dm_exec_connections

Correct Answer: B

Explanation: dm_exec_query_stats aggregates runtime stats per cached plan handle.

Q24: Most effective technique to reduce large intermediates?

 A) TOP with ORDER BY

 B) Replace DISTINCT with GROUP BY
 C) Filter early with WHERE
 D) Use triggers

Correct Answer: C

Explanation: Predicate pushdown reduces rows as soon as possible.

Q25: Which operator indicates parallelism in plans?

 A) Parallelism (Repartition Streams)

 B) Hash Match
 C) Index Scan
 D) Sort

Correct Answer: A

Explanation: The Parallelism (Exchange) family shows data movement among threads.

Q26: Best rewrite for wide OR predicates?

 A) Replace with UNION ALL of simpler predicates

 B) Use triggers
 C) Use CROSS JOIN
 D) Replace with DISTINCT

Correct Answer: A

Explanation: Splitting OR into separate sargable predicates can enable seeks.

Q27: Which clause to avoid in hot OLTP queries to reduce blocking?

 A) JOIN
 B) ORDER BY
 C) DISTINCT
 D) SELECT *

Correct Answer: B

Explanation: ORDER BY introduces sorts that add CPU/memory pressure and potential
blocking.

Q28: Which hint ignores cached plans for a statement?

 A) WITH (NOLOCK)
 B) OPTION (RECOMPILE)
 C) OPTION (OPTIMIZE FOR UNKNOWN)
 D) OPTION (FORCE ORDER)

Correct Answer: B

Explanation: RECOMPILE builds a fresh plan per execution.

Q29: Who chooses join algorithms (Nested/Hash/Merge)?

 A) SQL Profiler
 B) Query Optimizer
 C) Query Store
 D) Extended Events

Correct Answer: B

Explanation: The optimizer selects algorithms based on row estimates and costs.

Q30: Most efficient for inserting many rows from another table?

 A) INSERT ... VALUES

 B) INSERT ... SELECT
 C) Cursor inserts
 D) WHILE loop inserts
Correct Answer: B

Explanation: Set-based INSERT ... SELECT avoids RBAR (row-by-agonizing-row) patterns.

Q31: Benefit of indexed views?

 A) Reduce compilation
 B) Precompute and persist aggregations/joins
 C) Allow partition switching
 D) Avoid deadlocks

Correct Answer: B

Explanation: They materialize results and can be indexed to accelerate repeated analytics.

Q32: Which operator removes duplicates in a streaming fashion?

 A) Sort
 B) Stream Aggregate (DISTINCT)
 C) Nested Loop
 D) Hash Match

Correct Answer: B

Explanation: Stream Aggregate (with DISTINCT) enforces uniqueness when input is

ordered.

Q33: Which hint allows all CPUs?

 A) MAXDOP 0
 B) FORCE ORDER
 C) OPTIMIZE FOR UNKNOWN
 D) RECOMPILE

Correct Answer: A

Explanation: MAXDOP 0 permits the optimizer to use all schedulers subject to server limits.

Q34: Best tool for historical analysis of long-running queries?

 A) Activity Monitor
 B) Query Store
 C) SQL Profiler
 D) sp_who2

Correct Answer: B

Explanation: Query Store keeps time-series of runtime stats and plans.

Q35: Common cause of poor plan choices?

 A) Index fragmentation
 B) Outdated statistics
 C) TempDB size
 D) Frequent log backups

Correct Answer: B

Explanation: Bad stats lead to bad cardinality estimates and suboptimal plans.

Q36: Which logical phase executes first?

 A) SELECT
 B) WHERE
 C) GROUP BY
 D) FROM

Correct Answer: D

Explanation: Logical processing order starts with FROM → WHERE → GROUP BY → HAVING
→ SELECT → ORDER BY.

Q37: Which feature adjusts strategies during execution based on feedback?

 A) Query Store Hints

 B) Adaptive Query Processing
 C) Indexed Views
 D) Plan Guides

Correct Answer: B

Explanation: Adaptive features (e.g., memory grant feedback) refine plans at runtime.

Q38: Which clause often hurts performance when wrapping indexed columns?
 A) SELECT specific columns
 B) Functions on indexed columns in WHERE
 C) Filter early
 D) Use EXISTS

Correct Answer: B

Explanation: Expressions like WHERE YEAR(datecol)=... inhibit seeks; rewrite to range

predicates.

Q39: Fastest for existence checks?

 A) SELECT COUNT(*)
 B) EXISTS
 C) IN
 D) JOIN

Correct Answer: B

Explanation: EXISTS stops after first match; COUNT must scan all qualifying rows.

Q40: DBCC FREEPROCCACHE does what?

 A) Clears buffer cache

 B) Clears plan cache
 C) Drops temp tables
 D) Shrinks log

Correct Answer: B

Explanation: It removes cached plans; use carefully.

Q41: Most efficient join when both inputs are sorted?

 A) Nested Loops
 B) Hash Match
 C) Merge Join
 D) Cross Join

Correct Answer: C

Explanation: Merge Join leverages sorted inputs to avoid hashing/probing.

Q42: What is a filtered index?

 A) Index on clustered keys only

 B) Index on a subset of rows via a predicate
 C) Index on all columns
 D) In-memory-only index

Correct Answer: B

Explanation: Filtered indexes target frequently queried subsets, reducing size and cost.

Q43: Function to fetch a cached plan by handle?

 A) sys.fn_get_sql()
 B) sys.dm_exec_query_plan()
 C) sys.dm_exec_requests
 D) sys.dm_exec_cached_plans

Correct Answer: B

Explanation: sys.dm_exec_query_plan(plan_handle) returns the plan XML.

Q44: Which option automatically creates stats when needed?

 A) AUTO_CREATE_STATISTICS
 B) AUTO_UPDATE_STATISTICS
 C) AUTO_SHRINK
 D) AUTO_CLOSE

Correct Answer: A

Explanation: AUTO_CREATE_STATISTICS builds column stats to aid the optimizer.

Q45: Operator used for hashing large datasets (joins/aggregations)?

 A) Nested Loops
 B) Merge Join
 C) Hash Match
 D) Stream Aggregate

Correct Answer: C
Explanation: Hash Match builds hash tables to join/probe efficiently on big unsorted inputs.

Q46: Function for row numbering?

 A) NTILE()
 B) RANK()
 C) ROW_NUMBER()
 D) PARTITION BY

Correct Answer: C

Explanation: ROW_NUMBER() assigns sequential numbers per partition/order.

Q47: Which feature can force a stable plan automatically?

 A) Query Store
 B) Database Tuning Advisor
 C) Extended Events
 D) DDL Triggers

Correct Answer: A

Explanation: Query Store supports plan forcing to stabilize performance.

Q48: Which expression style performs better for conditional logic in SELECT?

 A) CASE
 B) IF...ELSE
 C) Nested SELECT
 D) CROSS APPLY

Correct Answer: A

Explanation: CASE is set-based and evaluated per row efficiently.

Q49: Benefit of batch mode execution with columnstore?

 A) Reads fewer rows

 B) Processes many rows per batch for higher throughput
 C) Forces parallelism
 D) Uses key lookups
Correct Answer: B

Explanation: Batch mode operates on row groups, improving CPU efficiency.

Q50: Rewrite that improves many NOT IN patterns (with NULLs)?

 A) EXCEPT
 B) NOT EXISTS
 C) CROSS JOIN
 D) UNION ALL

Correct Answer: B

Explanation: NOT EXISTS handles NULL semantics and is often sargable.

Q51: DMV for currently executing queries?

 A) sys.dm_exec_sessions
 B) sys.dm_exec_requests
 C) sys.dm_exec_query_stats
 D) sys.dm_exec_connections

Correct Answer: B

Explanation: dm_exec_requests lists active requests with waits, blocking, etc.

Q52: Operator that enforces uniqueness via sorting?

 A) Sort (Distinct Sort)

 B) Stream Aggregate
 C) Nested Loop
 D) Key Lookup

Correct Answer: A

Explanation: Distinct Sort removes duplicates by sorting and discarding dupes.

Q53: Impact of scalar UDFs in SELECT?

 A) Improve performance via parallelism

 B) Often degrade performance due to row-by-row execution
 C) Force clustered scans
 D) Inline automatically

Correct Answer: B

Explanation: Scalar UDFs are evaluated per row and can inhibit parallelism (pre-inlining
engines).

Q54: Operator that processes partitions independently?

 A) Window Aggregate
 B) Partitioned Aggregate
 C) Hash Match
 D) Filter

Correct Answer: B

Explanation: Partitioned Aggregate aggregates per partition efficiently.

Q55: Option to show estimated plan without running the query?

 A) SET SHOWPLAN_ALL ON
 B) SET STATISTICS IO ON
 C) DBCC SHOW_STATISTICS
 D) SET STATISTICS TIME ON

Correct Answer: A

Explanation: SHOWPLAN shows the optimizer’s chosen plan without execution.

Q56: Which feature prevents parameter sniffing by recompiling with literals?

 A) RECOMPILE
 B) OPTIMIZE FOR UNKNOWN
 C) FORCE ORDER
 D) AUTO_CREATE_STATISTICS

Correct Answer: A

Explanation: RECOMPILE generates a fresh plan per execution using supplied literals.

Q57: Best join for small, repeated lookups in OLTP?

 A) Nested Loops
 B) Hash Join
 C) Merge Join
 D) Cross Join

Correct Answer: A

Explanation: Nested Loops excel when the outer input is small and inner side is indexed.

Q58: Key benefit of CROSS APPLY over JOIN?

 A) Supports correlated TVFs/subqueries

 B) Ignores NULLs
 C) Forces index usage
 D) Avoids sniffing

Correct Answer: A

Explanation: CROSS APPLY evaluates a table-valued function per outer row (correlation).

Q59: Which option helps keep stats fresh automatically?

 A) AUTO_UPDATE_STATISTICS
 B) AUTO_SHRINK
 C) AUTO_CREATE_STATISTICS
 D) DBCC UPDATEUSAGE

Correct Answer: A

Explanation: AUTO_UPDATE_STATISTICS refreshes stale stats based on thresholds.

Q60: Tool to analyze workload and suggest indexes?

 A) Database Tuning Advisor

 B) Activity Monitor
 C) Query Store
 D) Profiler

Correct Answer: A

Explanation: DTA consumes a workload and proposes indexes/partitioning.