Home » Microsoft » MB6-886 » Which action should you take?
You are a database developer on an instance of SQL Server.
Your Prod database currently contains a view named OrderDetailsView that joins five tables.
Users will frequently perform queries using the view, and you want to improve the performance of these queries.
Which action should you take?
A. Create a filtered index on the view.
B. Partition the view.
C. Create a full-table nonclustered index on the view.
D. Create a unique clustered index on the view.
Correct Answer: D
Explanation/Reference:
Creating a unique clustered index on a view increases the view’s performance. Views with clustered indexes are referred to as indexed views. In an indexed view, the result set is stored in the database, similar to storing a table with a clustered index. Because a result set does not have to be generated each time the view is referenced, it can drastically improve performance. To create an indexed view, the view must use schema binding. Therefore, in this scenario, you might need to alter the view to include the WITH SCHEMABINDING clause for the index to be created successfully on the view. After you create an indexed view, the optimizer will use the view index even for queries that do not directly reference the view in the FROM clause.
You should not create a full-table nonclustered index on the view. To index a view, you create a unique clustered index on the view, not a nonclustered index.
You should not partition the view. Partitioned views are used when you have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views are implemented by creating a view that queries several tables and combines the results using the UNION ALL operator. Although in some situations a partitioned view is helpful, one would not be applicable in this scenario.
You should not create a filtered index on the view. A filtered index is a nonclustered index that is defined including a specific WHERE clause to optimize the index for specific queries. The index uses the WHERE clause condition to index only specific rows in the table. Using a filtered index can improve performance in many situations and can reduce the space required for the index. However, a filtered index cannot be created on a view.