You develop a Microsoft SQL Server 2012 database. You create a view that performs the following
tasks:
Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports.
You need to improve the performance of the reports.
What should you do?
A.
Convert the view into a table-valued function.
B.
Convert the view into a Common Table Expression (CTE).
C.
Convert the view into an indexed view.
D.
Convert the view into a stored procedure and retrieve the result from the stored procedure into a
temporary table.
Explanation:
http://msdn.microsoft.com/en-us/library/ms191432.aspx
From that page:
The SELECT statement in the view definition must not contain the following Transact-SQL elements:
(COUNT(), MIN(), Max(), AVG(), HAVING() are forbidden)
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 can reference only the columns specified in the GROUP BY clause
I found the restrictions so restrictive that I actually never succeeded in using an aggregated query in an indexed view!
0
0
what you’re talking about?
Convert to indexed view.
0
0
BTW, part of the new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0