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?
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
Explanation:
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms180026.aspx
Reference: http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
Answer A does not give you the requested information. We need to build a resultset that “includes aggregate information about the total number of global sales”.
Answer A gives us the total number of sales that includes both domestic and international sales orders. We cannot distill the total number of global sales from that. It’s like getting the total number of children in a classroom, while we need to know the total number of boys.
We need to look at answers C and D. Since UNION ALL is faster than a UNION (because UNION needs to perform an additional DISTINCT), the correct answer should be D.
1
0
Thinking about this a bit more, ‘global sales’ might just mean all domestic + international sales. In that case A is indeed correct.
1
0
This makes A valid good one I thought it was D but with this tip A is correct.
0
0
The data in
the two tables is distinct from one another
0
0
I would say D
0
0
i would say A, as jack said, We need to build a resultset that “includes aggregate information about the total number of global sales”.
but both tables contain more than 100 million rows,its a large nubmer,if you part it,
will you want your leadership to do calculations by their self ?
0
0
What’s more, part of new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0
A
0
0