You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties at Domain.com encompass administrating SQL Server 2005 database named Certkiller -DB08.
You have received instruction from the CIO to implementing a series of views that are used in ad hoc queries. The views are used to put into effect the Domain.com security policy of abstracting data. You notice that some of these views perform slowly therefore you create indexes on those views to increase the performance. You do this whilst maintaining the company’s security policy. The current date is returned by one of the views as one of the columns. The current date returned by the view is returned by using the GETDATE() function. This view does not permit you to create an index. You need to create an index on the view.
What should you do? (Choose all that apply)
A.
All nondeterministic function calls should be removed from within the view.
B.
Create the view with the WITH CHECK OPTION phrase.
C.
Remove all the deterministic function calls from within the view.
D.
Schema-bind all the functions that are called from within the view.
Explanation:
The GETDATE() is a nondeterministic function, meaning that it does NOT have to return the same value every times it is called, and therefore the results of the function can not be indexed. When you specify the SCHEMABINDING option, you cannot drop any tables, views, or functions referenced by the view without first dropping the view.