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, SalesAmountFROM
DomesticSalesOrders
UNION ALL SELECT
SalesOrderId,
SalesAmountFROM
InternationalSalesOrder s ) AS p
B.
SELECT COUNT(*) AS
NumberOfSales,
SUM(SalesAmount) AS
TotalSalesAmount
FROM (SELECT SalesOrderId, SalesAmountFROM
DomesticSalesOrders
UNION SELECT
SalesOrderId,
SalesAmountFROM
InternationalSalesOrder s ) AS p
C.
SELECT COUNT(*) AS
NumberOfSales,
SUM(SalesAmount) AS
TotalSalesAmountFROM DomesticSalesOrders
UNION SELECT COUNT(*) AS
NumberOfSales,
SUM(SalesAmount) AS
TotalSalesAmountFROM InternationalSalesOrders
D.
SELECT COUNT(*) AS
NumberOfSales,
SUM(SalesAmount) AS
TotalSalesAmountFROM DomesticSalesOrders
UNION ALL SELECT COUNT(*) AS NumberOfSales,
SUM(SalesAmount) AS
TotalSalesAmountFROM InternationalSalesOrders
Explanation:
http://msdn.microsoft.com/en-us/library/ms180026.aspx
http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimalperformance-comparison/