A view is a stored SQL SELECT statement, that can be used in other DML operations exactly like a table. Whereas the tables in a database may be implemented to achieve the most efficient storage arrangement, views can be used to present data in a more user-friendly fashion.

Creating Views

Views are created with the 'CREATE VIEW' statement:

CREATE VIEW view_name
WITH [view_attribute, ....]
AS SELECT (...)

The SELECT statement used to create a view can reference tables, other views or functions. Views can also be created using UNION or UNION_ALL to combine multiple tables. Views cannot:

1. Contain COMPUTE or COMPUTE_BY clauses
2. Use INTO
3. Use OPTION
4. Reference a temporary table or variable or any type
5. Use ORDER_BY unless TOP is also used

When a view is used, SQL Server replaces the view name with the definition of the view. Views can be used to modify data under the following conditions:

• The data modification must reference one and only one table
• Columns in the view must directly reference columns in the table - no aggregates, unions, cross joins, except or intersects are allowed
• Columns modified must not be affected by distinct, group by or having
• The TOP operator must not be used

Materialised Views

An index can be added to a view. To create the index, the SELECT statement for the view has to be executed and the resultset stored. This process results in a 'materialised' view. Changes to the underlying tables, will cause SQL Server to update the stored resultset and index. Materialised views thus have a storage and processing overhead.

Several conditions must be satisfied to create a materialised view:

• The SELECT statement cannot reference other views
• Statistical functions cannot be used in the view definition
• The index created must be clustered and unique
• The SCHEMABINDING option is required

CREATE VIEW view_name
WITH SCHEMABINDING
AS SELECT(...)

CREATE UNIQUE CLUSTERED INDEX iucx_view_name
ON view_name(column_list)