Scenarios for Using Views
Views are generally used to focus, simplify, and customize the perception each user has of the database. Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view. Views can be used to provide a backward compatible interface to emulate a table that used to exist but whose schema has changed. Views can also be used when you copy data to and from Microsoft SQL Server 2005 to improve performance and to partition data.
To Focus on Specific Data
Views let users focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary or sensitive data can be left out of the view.
For example, a view vBikes in the AdventureWorks sample database would let a user see the names of all bicycles that are currently in stock. The view filters out all fields from the Product table except Name, and returns only names of finished bicycles instead of bicycle components.
CREATE VIEW vBikes AS
SELECT DISTINCT p.[Name] FROM Production.Product p
JOIN Production.ProductInventory i ON p.ProductID = i.ProductID
JOIN Production.ProductSubCategory ps
ON p.ProductSubcategoryID = ps.ProductSubCategoryID
JOIN Production.ProductCategory pc
ON (ps.ProductCategoryID = pc.ProductCategoryID
AND pc.Name = N'Bikes')
AND i.Quantity > 0
To Simplify Data Manipulation
Views can simplify how users work with data. You can define frequently used joins, projections, UNION queries, and SELECT queries as views so that users do not have to specify all the conditions and qualifications every time an additional operation is performed on that data. For example, a complex query that is used for reporting purposes and performs subqueries, outer joins, and aggregation to retrieve data from a group of tables can be created as a view. The view simplifies access to the data because the underlying query does not have to be written or submitted every time the report is generated; the view is queried instead. For more information about manipulating data, see Query Fundamentals.
Although not a complex query, view vBikes in the AdventureWorks sample database lets users focus on specific data without having to construct the JOIN clauses that are required to produce the view.
You can also create inline user-defined functions that logically operate as parameterized views, or views that have parameters in WHERE-clause search conditions or other parts of the query. For more information, see Inline User-defined Functions.
To Provide Backward Compatibility
Views enable you to create a backward compatible interface for a table when its schema changes. For example, an application may have referenced a nonnormalized table that has the following schema:
Employee(Name, BirthDate, Salary, Department, BuildingName)
To avoid redundantly storing data in the database, you could decide to normalize the table by splitting it into the following two tables:
Employee2(Name, BirthDate, Salary, DeptId)
Department(DeptId, BuildingName)
To provide a backward-compatible interface that still references data from Employee, you can drop the old Employee table and replace it by the following view:
CREATE VIEW Employee AS
SELECT Name, BirthDate, Salary, BuildingName
FROM Employee2 e, Department d
WHERE e.DeptId = d.DeptId
Applications that used to query the Employee table can now to obtain their data from the Employee view. The application does not have to be changed if it only reads from Employee. Applications that update Employee can sometimes also be supported by adding INSTEAD OF triggers to the new view to map INSERT, DELETE, and UPDATE operations on the view to the underlying tables. For more information, see Designing INSTEAD OF Triggers.
To Customize Data
Views let different users to see data in different ways, even when they are using the same data at the same time. This is especially useful when users who have many different interests and skill levels share the same database. For example, a view can be created that retrieves only the data for the customers with whom an account manager deals. The view can determine which data to retrieve based on the login ID of the account manager who uses the view.
To Export and Import Data
Views can be used to export data to other applications. For example, you may want to use the Customer and SalesOrderHeader tables in the AdventureWorks database to analyze sales data using Microsoft Excel. To do this, you can create a view based on the Customer and SalesOrderHeader tables. You can then use the bcp utility to export the data defined by the view. Data can also be imported into certain views from data files by using the bcp utility or BULK INSERT statement providing that rows can be inserted into the view using the INSERT statement. For more information about the restrictions for copying data into views, see INSERT (Transact-SQL). For more information about how to use the bcp utility and BULK INSERT statement to copy data to and from a view, see Bulk Exporting Data from or Bulk Importing Data to a View.
To Combine Partitioned Data Across Servers
The Transact-SQL UNION set operator can be used within a view to combine the results of two or more queries from separate tables into a single result set. This appears to the user as a single table that is called a partitioned view. For example, if one table contains sales data for Washington, and another table contains sales data for California, a view could be created from the UNION of those tables. The view represents the sales data for both regions.
To use partitioned views, you create several identical tables, specifying a constraint to determine the range of data that can be added to each table. The view is then created that uses these base tables. When the view is queried, SQL Server automatically determines which tables are affected by the query and references only those tables. For example, if a query specifies that only sales data for the state of Washington is required, SQL Server reads only the table that contains the Washington sales data; no other tables are accessed.
Partitioned views can be based on data from multiple heterogeneous sources, such as remote servers, to create a federation of database servers. For example, to combine data from different remote servers each of which stores data for a different region of your organization, you can create distributed queries that retrieve data from each data source, and then create a view based on those distributed queries. Any queries read only data from the tables on the remote servers that contains the data requested by the query; the other servers referenced by the distributed queries in the view are not accessed.
When you partition data across multiple servers, queries accessing only a fraction of the data can run faster because there is less data to scan. If the tables are located on different servers, or on a computer that uses multiple processors, each table involved in the query can also be scanned in parallel. This can improve query performance. Additionally, maintenance tasks, such as rebuilding indexes or backing up a table, can execute more quickly.
By using a partitioned view, the data still appears as a single table and can be queried as such without having to manually reference the correct underlying table.
Note
The preferred method for partitioning data local to one server is through partitioned tables. For more information, see Partitioned Tables and Indexes.
Partitioned views are updatable if either of the following conditions is met:
- An INSTEAD OF trigger is defined on the view with logic to support INSERT, UPDATE, and DELETE statements.
- Both the view and the INSERT, UPDATE, and DELETE statements follow the rules defined for updatable partitioned views. For more information, see Creating Partitioned Views.
See Also
Concepts
Join Fundamentals
Using Partitioned Views
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|