Lesson 1:Introduction to Views | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

Views are generally used to focus, simplify, and customize each user's perception of the database. You can use a view as a security mechanism by allowing a user to access data through the view without granting the user permission to directly access the underlying base tables of the view. You can also use views to improve performance and to partition data when you are copying data to and from SQL Server 2000. This lesson will introduce you to views and the various functionalities that they support.

After this lesson, you will be able to:

Define what a view is and describe the functionalities that views support.

Estimated lesson time: 20 minutes

Overview of Views

A view acts as a filter on the underlying tables referenced in the view. The query that defines the view can be from one or more tables or from other views in the current database or other databases. You can also use distributed queries to define views that use data from multiple heterogeneous sources. This functionality is useful, for example, if you want to combine similarly structured data from different servers, each of which stores data for a different region of your organization.

A view can be thought of as either a virtual table or a stored query. The data accessible through a standard 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 in the same way a table is referenced.

Figure 10.1 shows a view based on a SELECT statement that retrieves data from the Titles table and the Publishers table in the Pubs database.

There are no restrictions on querying through views and few restrictions on modifying data through them. In addition, a view can reference another view.

You can use a view to perform any or all of the following functions:

Restricting a user to specific rows in a table
Restricting a user to specific columns
Joining columns from multiple tables so that they look like a single table
Aggregating information instead of supplying details

Views can be used to partition data across multiple databases or instances of SQL Server 2000. Partitioned views enable you to distribute database processing across a group of servers.

figure 10.1-a view based on data from two tables.

Figure 10.1 A view based on data from two tables.

SQL Server 2000 also supports indexed views, which greatly improve the performance of complex views such as those usually found in data warehouses or other decision support systems. With a standard view, the result set is not saved in the database. Rather, it is dynamically incorporated into the logic of the statement and is built dynamically at run time.

Complex queries, however, 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.

Scenarios for Using Views

You can use views in a variety of ways to return data.

To Focus on Specific Data

Views enable users to focus on specific data that interests them and on the specific tasks for which they are responsible. You can leave out unnecessary data in the view. This action also increases the security of the data, because users can see only the data that is defined in the view and not the data in the underlying table.

To Simplify Data Manipulation

Views can simplify how users manipulate data. You can define frequently used joins, UNION queries, and SELECT queries as views so that users do not have to specify all of the conditions and qualifications each time an additional operation is performed on that data. For example, a complex query that is used for reporting purposes and that 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 each time the report is generated. The view is queried instead.

You can also create inline user-defined functions that operate logically as parameterized views (views that have parameters in WHERE-clause search conditions).

To Customize Data

Views enable different users to see data in different ways, even when they are using the same data concurrently. This feature is particularly advantageous 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

You can use views to export data to other applications. For example, you might want to use the Stores and Sales tables in the Pubs database to analyze sales data in Microsoft Excel. To perform this task, you can create a view based on the Stores and Sales 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 the BULK INSERT statement, providing that rows can be inserted into the view by using the INSERT statement.

To Combine Partitioned Data

The Transact-SQL UNION set operator can be used within a view to combine the result of two or more queries from separate tables into a single result set. This display appears to the user as a single table (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, 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 by using 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 containing the Washington sales data, no other tables are accessed.

Partitioned views can be based on data from multiple heterogeneous sources (such as remote servers), not just from tables in the same database. 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 you can then create a view based on those distributed queries. Any queries read only data from the tables on the remote servers that contain 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 tables or 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 has multiple processors, each table involved in the query can also be scanned in parallel, thereby improving query performance. Additionally, maintenance tasks (such as rebuilding indexes or backing up a table) can be executed more quickly.

By using a partitioned view, the data still appears as a single table and can be queried as such without having to reference the correct underlying table manually.

Partitioned views are updateable if either of these 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 updateable, partitioned views.

Lesson Summary

Views are generally used to focus, simplify, and customize each user's perception of the database. A view acts as a filter on the underlying tables referenced in the view. A view can be thought of as either a virtual table or a stored query. There are no restrictions on querying through views, and few restrictions exist on modifying data through them. Views enable you to focus on specific data, simplify data manipulation, customize data, export and import data, and combine partitioned data.