SQL Views
A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions:
- Restrict a user to specific rows in a table.
For example, allow an employee to see only the rows recording his or her work in a labor-tracking table.
- Restrict a user to specific columns.
For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.
- Join columns from multiple tables so that they look like a single table.
- Aggregate information instead of supplying details.
For example, present the sum of a column, or the maximum or minimum value from a column.
Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, titleview in the pubs database is a view that selects data from three base tables to present a virtual table of commonly needed data:
CREATE VIEW titleview
AS
SELECT title, au_ord, au_lname, price, ytd_sales, pub_id
FROM authors AS a
JOIN titleauthor AS ta ON (a.au_id = ta.au_id)
JOIN titles AS t ON (t.title_id = ta.title_id)
You can then reference titleview in statements in the same way you would reference a table:
SELECT *
FROM titleview
A view can reference another view. For example, titleview presents information that is useful for managers, but a company typically discloses year-to-date figures only in quarterly or annual financial statements. A view can be built that selects all the titleview columns except au_ord and ytd_sales. This new view can be used by customers to get lists of available books without seeing the financial information:
CREATE VIEW Cust_titleview
AS
SELECT title, au_lname, price, pub_id
FROM titleview
Views can be used to partition data across multiple databases or instances of Microsoft® SQL Server™ 2000. Partitioned views can be used to distribute database processing across a group of servers. The group of servers has the same performance benefits as a cluster of servers, and can be used to support the processing needs of the largest Web sites or corporate data centers. An original table is subdivided into several member tables, each of which has a subset of the rows from the original table. Each member table can be placed in databases on separate servers. Each server also gets a partitioned view. The partitioned view uses the Transact-SQL UNION operator to combine the results of selects against all the member tables into a single result set that behaves exactly like a copy of the full original table. For example, a table is partitioned across three servers. On the first server you define a partitioned view similar to this:
CREATE VIEW PartitionedView AS
SELECT *
FROM MyDatabase.dbo.PartitionTable1
UNION ALL
SELECT *
FROM Server2.MyDatabase.dbo.PartitionTable2
UNION ALL
SELECT *
FROM Server3.MyDatabase.dbo.PartitionTable3
You define similar partitioned views on each of the other servers. With these three views, any Transact-SQL statements on any of the three servers that reference PartitionedView will see the same behavior as from the original table. It is as if a copy of the original table exists on each server, when in fact there is only one member table and a partitioned view on each table. For more information, see Scenarios for Using Views.
Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view, for example:
-- Increase the prices for publisher '0736' by 10%.
UPDATE titleview
SET price = price * 1.10
WHERE pub_id = '0736'
GO
SQL Server 2000 supports more complex types of INSERT, UPDATE, and DELETE statements that reference views. INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. Also, partitioned views support INSERT, UDPATE, and DELETE statements that modify multiple member tables referenced by the view.
Indexed views are a SQL Server 2000 feature that greatly improves the performance of complex views of the type usually found in data warehouses or other decision support systems.
Views are called virtual tables because the result set of a view is us not usually saved in the database The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time. For more information, see View Resolution.
Complex queries, such as those in decision support systems, can reference large numbers of rows in base tables, and aggregate large amounts of information into relatively concise aggregates such as sums or averages. SQL Server 2000 supports creating a clustered index on a view that implements such a complex query. When the CREATE INDEX statement is executed the result set of the view SELECT is stored permanently in the database. Future SQL statements that reference the view will have substantially better response times. Modifications to the base data are automatically reflected in the view.
The SQL Server 2000 CREATE VIEW statement supports a SCHEMABINDING option that prevents the tables referenced by the view being changed without adjusting the view. You must specify SCHEMABINDING for any view on which you create an index.