Home » Microsoft » MB6-886 » What should you use?
You have a table that has 10 million rows. The table has a view that returns all of the rows.
You discover performance degradation when you run an unfiltered view.
You need to recommend a solution to replace the view. The solution must require that the returned data is filtered by a parameter.
What should you use?
A. an indexed view
B. a scalar function
C. a table-valued function
D. a table-valued type
Correct Answer: C
Explanation/Reference:
User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views. Table-valued functions and Inline functions can be also used to achieve the functionality of parameterized views.
In the current scenario, we should replace the view by a table-valued function which takes filter as a parameter and return data filtered by this parameter. In this case we will force users to specify a parameter before querying the table.
