⚡️ Pass with Confidence Sale - 40% off ALL packages! ⚡️

2 days, 2 hours remaining!
Crucial Exams

CompTIA Data+ DA0-002 (V2) Practice Question

During a performance review you discover that a reporting query contains this pattern:

SELECT ...
FROM (
    SELECT CustomerID, SUM(TotalDue) AS TotalSpent
    FROM dbo.Orders
    WHERE OrderDate >= '2024-01-01'
    GROUP BY CustomerID
) AS recent_orders
JOIN dbo.Orders o1 ON o1.CustomerID = recent_orders.CustomerID
JOIN dbo.Orders o2 ON o2.CustomerID = recent_orders.CustomerID;

The execution plan shows the derived subquery against the 50-million-row Orders table is executed three times, causing very high logical reads. Without changing the final results, which action is most likely to reduce execution time and I/O?

  • Insert the subquery results into a local temporary table (#recent_orders), add an index on CustomerID, and join the main query to that temporary table.

  • Rewrite the derived subquery as a common table expression (CTE) so SQL Server can cache the result internally.

  • Add an OPTION (FORCESEEK) hint to every Orders reference to force index seeks during each scan.

  • Add WITH (NOLOCK) hints to all Orders references to avoid locking during the scans.

CompTIA Data+ DA0-002 (V2)
Data Acquisition and Preparation
Your Score:
Settings & Objectives
Random Mixed
Questions are selected randomly from all chosen topics, with a preference for those you haven’t seen before. You may see several questions from the same objective or domain in a row.
Rotate by Objective
Questions cycle through each objective or domain in turn, helping you avoid long streaks of questions from the same area. You may see some repeat questions, but the distribution will be more balanced across topics.

Check or uncheck an objective to set which questions you will receive.

Wipe Score
Bash, the Crucial Exams Chat Bot
AI Bot