You have a view that includes an aggregate. You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses. You need to ensure that you can update the view. What should you create?
Suggested Answer:B🗳️
When you use the SchemaBinding keyword while creating a view or function you bind the structure of any underlying tables or views. Itmeans that as long as that schemabound object exists as a schemabound object (ie you don't remove schemabinding) you are limited in changes that can be made to the tables or views that it refers to. References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/
D - DML Trigger (Instead of). If you have an aggreate in view you need some complex logic to calculate how to change tables that are a base a view (that the view is referencing).
this question doesnt make sense.
here is the original question :
You have a view that includes an aggregate.
You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.
You need to ensure that you can update the view.
What should you create?
A. a nonclustered index
B. a schema-bound view
C. a stored procedure
D. an INSTEAD OF trigger
and the answer was D
I agree with Andy - schema-binding limits structural changes to the underlying tables for the view - which is not what this question seems to be about.
Because there is an aggregate column, the other columns may represent group by columns - so a change in any of their values would necessitate a change in the aggregate column (and potentially other columns as well). A trigger on the view would be a way to clean this up and keep the view rows internally consistent.
schema bounding relates to structural changes in the underlying tables to the changing on values if I understand it correctly looks like B isn't an option
C is also right answer. Partitioned views allow to modify the data in tables with several restrictions. https://docs.microsoft.com/en-gb/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15
A partitioned view is a view that is based on a query that uses a UNION ALL set operator
to treat multiple tables as one. Before the feature of partitioning tables and indexes was
created, it was the primary way to give an administrator the ability to manage multiple
“slices” of a table as different physical resources.
The feature still exists, both for backward compatibility (since partitioning is the typical
best way to implement partitioning since 2005), and to enable a VIEW object to work
across multiple independent federated SQL Servers. Generally, the place where this
feature is still the best practice is a case such as having two or more servers located in
different corporate locations. Each location might have a copy of their data, and then a
view is created that lets you treat the table as one on the local server.
However an updatable view must meet certain conditions, and an updatable partitioned view has further conditions it must meet.
From the manual we can see that the view as described cannot be an updateable view due to the aggregated column in it.
"The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following: An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP"
Because of this we have to use an INSTEAD OF Trigger (DML trigger)
exactly, in particular as the view has an aggregate. I agree
upvoted 7 times
...
This section is not available anymore. Please use the main Exam Page.70-762 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
LilahM
Highly Voted 5 years, 8 months agoHoglet
4 years, 4 months agoSzalonyZielonyRobak
Highly Voted 5 years, 2 months agoUsefJuan
Most Recent 4 years, 6 months agoHoglet
4 years, 4 months agoSoupDJ
4 years, 6 months agoAndy7622
4 years, 7 months agoNew_user
5 years, 4 months agoJohnFan
5 years, 2 months agoHoglet
4 years, 4 months agoRavenVStar
5 years, 4 months agoDieter
5 years, 8 months ago