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?
A. Create a partitioned view that allows users to update only their respective partitions.
B. Create a view that includes a CTE.
C. Create a view that includes both tables and define INSTEAD OF triggers to allow DML operations.
D. Create a parameterized stored procedure and create a view on the returned result set.
Correct Answer: C
Explanation/Reference:
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 TaskView
AS
SELECT T.TaskID
,T.Description
,T.TaskTypeID
,T.DateAdded
,T.DateDue
,TT.TaskTypeID AS TypeID
,TT.Description AS TypeDescription
FROM Task AS T INNER JOIN TaskType AS TT
ON T.TaskTypeID = TT.TaskTypeID
Then you are limited by modifications which affect only one base table. For example, you can run the following queries:
--affect only Task table
INSERT INTO TaskView ([Description],[TaskTypeID],[DateAdded],[DateDue])
VALUES(‘Some Text’, 1, GETDATE(), NULL)
GO
--affect only TaskType table
INSERT INTO TaskView (TypeID,TypeDescription)
VALUES (10, ‘Some Text’)
GO
But you cannot run something like this:
INSERT INTO TaskView ([Description],[TaskTypeID],[DateAdded],[DateDue], TypeID,TypeDescription)
VALUES(‘Some Text’, 20, GETDATE(), NULL, 20, ‘Some Text’)
GO
Msg 4405, Level 16, State 1, Line 1
View 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 accessible
You 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.