Your database contains the Donor and DonorDetail tables. You are creating a view. The view’s definition is a complex query that references the same subquery multiple times. You want to allow users to access to the data, but avoid the code complexity of the view’s definition. Which action should you take?
A. Create a partitioned view to implement the subquery.
B. Use a parameterized stored procedure instead of a view.
C. Include a Common Table Expression (CTE) in the view’s definition.
D. Create a temporary table containing the subquery results.
Correct Answer: C
Explanation/Reference:
You should include a Common Table Expression (CTE) in the view’s definition. Using a CTE in the view’s definition in this scenario makes the Transact-SQL code 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. A CTE can also be used when defining a view. 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. A CTE definition can only include one WITH clause, 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 can use CTEs as an alternative to using a view, temporary table, or subquery.
You should not create a partitioned view to implement the subquery. 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 identical structures, 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 use a parameterized stored procedure instead of a view. Although using parameterized stored procedures is a recommended practice when you create stored procedures, you do not require a parameterized stored procedure in this scenario, and it would require additional development effort to create one.
Parameterized stored procedures are stored procedures that accept input parameter values at runtime. The parameters can then be used within the procedure’s code. For example, you might use a parameterized stored procedure and use the input parameters to construct a DML statement rather than accepting a string containing the DML statement. This approach would help to prevent SQL injection attacks.
You should not create a temporary table containing the subquery results. Temporary tables are only available while the table is being used. Local temporary tables are available to a single user until the user disconnects. Global temporary tables are available until all users using the table disconnect. If you attempt to create a view on a temporary table, you will receive an error similar to the following:
Msg 4508, Level 16, State 1, Procedure MyView, Line 3Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.
