Which query should you use?

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?

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/



Leave a Reply 21

Your email address will not be published. Required fields are marked *


optimus executive vertical

optimus executive vertical

Why viewers still make use of to read news papers when in this technological globe
all is presented on net?

chicco

chicco

Form MSDN: Performance and query time

Note that UNION take longer time, since the duplicates must be removed after the two selects has ended.

Yommy O.

Yommy O.

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.

Yommy O.

Yommy O.

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).

Turen

Turen

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

funny pictures

funny pictures

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

Google

Google

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.

Cheap Banners

Cheap Banners

we came across a cool site that you just may well delight in. Take a look for those who want

satta matka

satta matka

here are some hyperlinks to websites that we link to simply because we believe they may be really worth visiting

make your own app

make your own app

usually posts some really intriguing stuff like this. If you are new to this site

Google

Google

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.

click site

click site

always a huge fan of linking to bloggers that I enjoy but dont get a lot of link enjoy from

doktor

doktor

Sites of interest we’ve a link to

Google

Google

Just beneath, are numerous completely not connected web-sites to ours, nonetheless, they’re certainly really worth going over.

how to create your own app

how to create your own app

very handful of websites that take place to become in depth beneath, from our point of view are undoubtedly properly really worth checking out

mypsychicadvice

mypsychicadvice

Here is a good Weblog You might Obtain Exciting that we Encourage You

Contact- AtlantaPiano

Contact- AtlantaPiano

just beneath, are a lot of entirely not connected web-sites to ours, nevertheless, they’re certainly really worth going over

Turen

Turen

please go to the websites we follow, which includes this a single, as it represents our picks in the web

how to make a app

how to make a app

check below, are some entirely unrelated web sites to ours, having said that, they’re most trustworthy sources that we use

cast iran globe valve

cast iran globe valve

we came across a cool web-site that you just may love. Take a look for those who want