SQL Server: Defining Views for Database Tables

Usually views are based on a SELECT statement and only provide query access to underlying data. However, DBA/Developer can create a view and then use INSTEAD OF triggers defined for the view to implement DML functionality. An INSTEAD OF trigger fires in place of the operation that fired the trigger. INSTEAD OF triggers can be used to prevent the original operation from taking place or to perform additional or alternative actions. DBA/Developer could create a view that made data in both tables available and then create the necessary INSTEAD OF triggers that would insert, update, or delete from both tables as needed. When DBA/Developer uses this approach, DBA/Developer must write DBA/Developer codes to specifically handle any constraints defined on the base tables' columns. Also, for IDENTITY columns, such as TaskID in the Task table, DBA/Developer would use the @@IDENTITY function to retrieve the last identity value before inserting or updating a record.DBA/Developer should not create a partitioned view that allows users to update only their respective partitions. Partitioned views are used when DBA/Developer has 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 DBA/Developer had three separate tables with an identical structure, DBA/Developer 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;

DBA/Developer 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 DBA/Developer creates the CTE, the statements 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. DBA/Developer 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 DBA/Developer previously created. DBA/Developer should also note that only one WITH 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.DBA/Developer should not create a parameterized stored procedure and create a view on the returned result set. Although DBA/Developer might choose to implement parameterized stored procedures to implement DML functionality, DBA/Developer cannot create a view over a result set.