Querying Microsoft SQL Server 2012/2014 — Question 58

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a
Primary Key column named SalesOrderId. The data in the two tables is distinct from one another.
Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.
You need to ensure that your query executes in the minimum possible time.
Which query should you use?

Answer options

• A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p
• B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p
• C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders
• D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders

Correct answer: A

Explanation

Option A is correct because it uses UNION ALL, which is more efficient for combining results from both tables since the data is distinct. Options B and C do not utilize UNION ALL, leading to potentially slower performance and incorrect aggregation. Option D, while using UNION ALL, incorrectly counts the total sales separately for each table instead of providing a combined total.