Many benefits are associated with the use of views. Here’s a summary of these benefits:
- Using views, users don’t query the tables directly; therefore, you are creating a security layer between users (or applications) and base tables. This, in turn, has another benefit: If the underlying database schema changes, you don’t have to change the application, just the views that access the tables.
- Views can be used to horizontally partition the data in a table. For example, suppose there is a table with three columns, but some users are allowed to see only two of these three columns. You can create a view that queries just the two columns they can see. Using this approach, these users will be able to issue a SELECT * query against the view, which is not possible with the table.
- Information schema views can be used as an alternative way to deal directly with system tables. They were introduced in SQL Server 7.0 as a method to provide information (metadata) about objects in SQL Server. The benefit of using these views is that the functionality of system tables might change in future releases of SQL Server, whereas these views’functionality will remain intact because they are ANSI standard.
- Indexes can be created on views. This is a new feature of SQL Server 2000, which basically stores the result set of a view in the database, or in other words, materializes the view. In general, the advantage of indexed views is that this makes queries run faster, because SQL Server can take advantage of the indexed view even if the view is not referenced in the query. When you create indexes on views, SQL Server automatically updates the data of the index. Therefore, whenever the data changes in the underlying tables, SQL Server updates the index.
- Another feature of SQL Server 2000 is the federated databases tech nology or distributed partitioned views that are updatable. This is Microsoft’s answer to the scale-out technology, in which the database is spread across many servers, each server containing a subset of the whole data. This technique is useful when you reach the point where scale-up (adding RAM, CPUs, and disks to the database server) is not enough, and the database server cannot scale any more for whatever reason.
- The trick is to create a view with the same name, in all the federated servers, that basically merges the data in all these servers using UNION ALL statements. Then, when users access data, SQL Server automatically takes the piece you need from the servers where it resides, making transparent to users the fact that data is split in more than one server. The benefit of this new feature is that these views are updatable, which allows applications to issue SELECT, INSERT, DELETE, and UPDATE statements against these views, and SQL Server does the rest (queries or modifies the data in the server where it resides).
- The last feature of SQL Server 2000 related to views is the introduction of instead-of triggers. In previous versions of SQL Server, triggers could not be defined on views. Now, this new type of trigger can be defined on views, which enhances tremendously the power of views in SQL Server. An instead-of trigger, as its name indicates, executes the code of the trigger instead of the triggering action (INSERT, UPDATE, or DELETE).
Source:Microsoft SQL Server 2000 Programming by Example
