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:
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-alloptimalperformance-comparison/
Why viewers still make use of to read news papers when in this technological globe
all is presented on net?
Form MSDN: Performance and query time
Note that UNION take longer time, since the duplicates must be removed after the two selects has ended.
The question says “The data in the two tables is distinct from one another.” Hence, there’s no need to remove duplicates. However, the UNION ALL clause is more effective, as the SQL Engine implicitly eliminates the SORT operation required to return a DISTINCT result set.
Option D aggregates the result set individually for both Domestic Sales and International Sales, and then merges the result into one contiguous result set; the UNION ALL eliminates duplicates (even though these may not be necessary).
Wonderful story, reckoned we could combine a few unrelated information, nonetheless really worth taking a search, whoa did a single master about Mid East has got extra problerms at the same time
Wonderful story, reckoned we could combine several unrelated data, nonetheless seriously worth taking a appear, whoa did 1 learn about Mid East has got much more problerms as well
The facts talked about in the post are a few of the most effective offered
That may be the end of this report. Here you will find some web pages that we assume you will appreciate, just click the links.
we came across a cool site that you just may well delight in. Take a look for those who want
here are some hyperlinks to websites that we link to simply because we believe they may be really worth visiting
usually posts some really intriguing stuff like this. If you are new to this site
Every as soon as inside a though we pick out blogs that we study. Listed below would be the most up-to-date web-sites that we choose.
always a huge fan of linking to bloggers that I enjoy but dont get a lot of link enjoy from
Sites of interest we’ve a link to
Just beneath, are numerous completely not connected web-sites to ours, nonetheless, they’re certainly really worth going over.
very handful of websites that take place to become in depth beneath, from our point of view are undoubtedly properly really worth checking out
Here is a good Weblog You might Obtain Exciting that we Encourage You
just beneath, are a lot of entirely not connected web-sites to ours, nevertheless, they’re certainly really worth going over
please go to the websites we follow, which includes this a single, as it represents our picks in the web
check below, are some entirely unrelated web sites to ours, having said that, they’re most trustworthy sources that we use
we came across a cool web-site that you just may love. Take a look for those who want