DRAG DROP
You use Microsoft SQL Server 2012 to develop a database application.
You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named
udf_price_index. You also need to ensure that the column supports an index.
Which three Transact-SQL statements should you use? (To answer, move the appropriate
SQL statements from the list of statements to the answer area and arrange them in the
correct order.)
Answer: 
Explanation:
i think it’s union all. and order is incorrect…
its union all and you for got the division and customer alias
From the 11 available statements, use : 5 then 8 then 2
Answer is 5, 6 then 2, as you need to preserve duplicates you will use Union All
I agree.
5,6,2 is the right answer
Yes 5, 6 and 2
The proposed solution does’n works: it isn’t possible to create an index on non-precise computed column not persisted. The computed column must be precise or must be presisted.
wops sorry: ignore previous post please. 🙂
no its actually union cause union doesnt give duplicates
oh wait sorry didnt see “retained”
Select DivisionID AS Division
,CustomerID AS Customer
from Div1Cust
UNION ALL
select DivisionID AS division
,CustomerID AS Customer
from Div2Cust
–UNION won’t do it because, we have to retain duplicate
–the other Selects Statements have the word DISTINCT in them which will
–eliminate duplicates. so, the answer should be 5,6,9
Answer is 5 ,6 , 2…. in first select query we have to give alias, union all use to merge table.
The answers are 5, 6, and 2.
This question is tricky by design if you notice: ” None of the rows in Div1Cust
exist in Div2Cust.”
Therefore, you could use UNION despite “Duplicates must be retained” because there are no duplicates.
However, UNION ALL performs better than UNION because it lacks SORT which makes it the better choice anyhow.