Which action should you take?

Correct Answer for the following Question is given below

You are a database developer on an instance of SQL Server 2008. Your database contains the Task and TaskType tables, which were defined with the following Transact-SQL statements:CREATE TABLE TaskType(TaskTypeID int PRIMARY KEY,Description varchar(40) NOT NULL);CREATE TABLE Task(TaskID int IDENTITY(1,1) PRIMARY KEY,Description varchar(50) NOT NULL,TaskTypeID int NOT NULL FOREIGN KEY REFERENCES TaskType(TaskTypeID),DateAdded datetime NOT NULL DEFAULT (GETDATE()),DateDue datetime);You need to create a view that makes data from both tables visible. You also want the view to be updatable.Which action should you take?You can modify the data of an underlying base table through a view, in the same manner as you modify data in a table by using UPDATE, INSERT and DELETE statements, but there are some restrictions. One of them is: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. It means that if you create a view like this:CREATE VIEW TaskViewASSELECT T.TaskID,T.Description,T.TaskTypeID,T.DateAdded,T.DateDue,TT.TaskTypeID AS TypeID,TT.Description AS TypeDescriptionFROM Task AS T INNER JOIN TaskType AS TTON T.TaskTypeID = TT.TaskTypeIDThen you are limited by modifications which affect only one base table. For example, you can run the following queries:–affect only Task tableINSERT INTO TaskView ([Description],[TaskTypeID],[DateAdded],[DateDue])VALUES(‘Some Text’, 1, GETDATE(), NULL)GO–affect only TaskType tableINSERT INTO TaskView (TypeID,TypeDescription)VALUES (10, ‘Some Text’)GOBut you cannot run something like this:INSERT INTO TaskView ([Description],[TaskTypeID],[DateAdded],[DateDue], TypeID,TypeDescription)VALUES(‘Some Text’, 20, GETDATE(), NULL, 20, ‘Some Text’)GOMsg 4405, Level 16, State 1, Line 1View or function ‘TaskView’ is not updatable because the modification affects multiple base tables.If the restrictions described previously prevent you from modifying data directly through a view, consider the use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.In this scenario, you could create a view that made data in both tables available and then create the necessary INSTEAD OF triggers on TaskView that would insert, update, or delete from both tables as needed. When you use this approach, you must write your code to specifically handle any constraints defined on the base tables’ columns. Also, for IDENTITY columns, such as TaskID in the Task table, you would use the SCOPE_IDENTITY() function to retrieve the last identity value before inserting or updating a record.You should not create a partitioned view that allows users to update only their respective partitions. 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 using the UNION ALL operator. For example, if you had three separate tables with an identical structure, you might use the following statement to create a partitioned view that allows users to query data from all three tables:CREATE VIEW PartView AS SELECT * FROM Table1 UNION ALL SELECT * FROM Table2 UNION ALL SELECT * FROM Table3;You should not create a view that includes a CTE. A Common Table Expression (CTE) would not be useful in this scenario. Using a CTE makes the Transact-SQL code, such as the view’s definition in this scenario, more readable than using a subquery. The syntax for creating a CTE is as follows:WITH expression_name [(column_name [,…n])]AS (CTE_query_definition)When defining a CTE, the WITH clause specifies the expression name that will be used in the subsequent SELECT statement. The WITH clause must contain a column list identifying the available columns, unless all columns in the expression’s query have distinct names. After you create the CTE, the statement immediately following the CTE definition can reference the CTE expression by name one or more times as if it were a table or view. Only the columns defined in the CTE expression are accessibleYou can also create two CTEs in a single WITH clause by separating the expressions with a comma. Within the WITH clause, the CTE can also reference itself or another CTE that you previously created. You should also note that only oneWITH clause is allowed, even if the query defining the CTE contains a subquery. In addition, a CTE query definition cannot contain an ORDER BY, COMPUTE, COMPUTE BY, INTO, FOR XML, or FOR BROWSE clause, or an OPTION clause that specifies query hints.You should not create a parameterized stored procedure and create a view on the returned result set. Although you might choose to implement parameterized stored procedures to implement DML functionality, you cannot create a view over a result set.

Question:

Which action should you take?

Options:

Create a partitioned view that allows users to update only their respective partitions.

Create a view that includes a CTE.

Create a view that includes both tables and define INSTEAD OF triggers to allow DML operations.

Create a parameterized stored procedure and create a view on the returned result set.

Correct Answer

The Correct Answer for this Question is

Create a view that includes both tables and define INSTEAD OF triggers to allow DML operations.

Explanation

The Question – Which action should you take? has been answered correctly and answers for the question is Create a view that includes both tables and define INSTEAD OF triggers to allow DML operations.

More about these Exams

These Exam Questions and the order of these questions keep changing. but the answers are obviously same. so if you don’t find a question after another we suggest you search it in the search box and we are sure you’ll find it. you can bookmark this site for Quick access in future.

We hope you found it helpful don’t forget to leave a comment if you feel a need to correct or ask we’re always here to help.

you can find more here at mnccertified

Feel free to contact via comment or email.

Happy Learning

Cheers, Team MNCcertified

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *