You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:
You create a view on the SalesDB database using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS
SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO
You want the SalesV view to persist data to disk in order to improve performance.
How would you accomplish this task?
A.
You should add a clustered index to the SalesV view.
B.
You should create a columnstore index on all columns used in the SalesV view.
C.
You should drop and recreate the SalesV view as a system view.
D.
You should drop and recreate the SalesV view as a partitioned view.
Explanation:
Ref: http://msdn.microsoft.com/en-us/library/ms190174
It should be A. You should add a clustered index to the SalesV view.
Reference: http://msdn.microsoft.com/en-us/library/ms191432.aspx
1
0
A for sure … an indexed view.
1
0
Even the ref doc says Indexed View
Indexed Views
An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table. You index a view by creating a unique clustered index on it. Indexed views can dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.
0
0
We’re having 2 joins in the view….So one clustered index is not enough to improve the view…
I think (B) is the correct response…Because a columnstore index would cover all view joins
0
0
Sorry i was wrong A is the correct answer…
2
0
?
0
0
What’s more, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0