DRAG DROP
You use a Microsoft SQL Server 2012 database.
You need to create an indexed view within the database for a report that displays Customer
Name and the total revenue for that customer.
Which four T-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:
Reference:
http://msdn.microsoft.com/en-us/library/ms191432.aspxRead all restrictions for indexed views.
Also read this useful
QUESTION
http://stackoverflow.com/questions/12419330/how-to-create-indexed-view-with-selectdistinct-statement-insql-2005
Something is wrong, I think it’ll be
CREATE …
SELECT …
GROUP …
CREATE UNIQUE CLUSTERED …
1
0
Yep, your answer is good.
0
0
Yes I think this is the correct answer
0
0
Yes it is correct . The answer given in explanation is wrong. you cant use aggregate ( SUM/Count) without grouping by . it will hits run time error in query browser.
0
0
https://msdn.microsoft.com/en-us/library/ms191432.aspx
0
0
The GROUP BY clause must be included. There’s no need to create a UNIQUE INDEX on customerID column of the view when a UNIQUE CLUSTERED INDEX will be even more efficient on the same column.
The SELECT statement in the view definition must contain the following Transact-SQL elements:
**If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.
**If the view definition contains a GROUP BY clause, the key of the unique clustered index (CustomerID) can reference only the columns specified in the GROUP BY clause (CustomerID, CustomerName).
Ref: https://msdn.microsoft.com/en-us/library/ms191432.aspx#Restrictions
0
0
Same Question on 70-461, Q 76. this is a wrong answer.
0
0
The Unique Index statement is not correct b/c the first index on a view has to be a unique clustered index since the view itself does not store any data.
0
0
The best thing about SQL Server is you can test it the answers and i just did this and here is what i found:
CREATE VIEW SalesLT.vwCustomerRevenue
WITH SCHEMABINDING
AS
SELECT
SO.CustomerID, C.FirstName +’ ‘+c.LastName AS CustomerName, SUM(so.SubTotal) AS CustomerTotal, COUNT_BIG(*) as RecCount
FROM SalesLT.Customer AS C inner join SalesLT.SalesOrderHeader AS SO
ON C.CustomerID = SO.CustomerID
GROUP BY so.CustomerID, C.FirstName +’ ‘+c.LastName
—-Succeeded
If you remove the GROUP BY clause then it fails as mentioned above so the GROUP BY is mandatory
CREATE UNIQUE CLUSTERED INDEX idx_vwCustomerRevenue ON SalesLT.vwCustomerRevenue (CustomerID);
–Surprisingly i didnt expect this to work but it did using CustomerID only
The last CREATE UNIQUE INDEX is using the same index name hence it would fail so only one of them is to be chosen
0
0
Besides, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0