When you are creating a view,
remember that a view is made up of an SQL statement that accesses the underlying data,
and that you control this SQL statement.
Also keep in mind the following guidelines,
which can help improve the performance, manageability, and usability of your databases:
* Use views for security.
Rather than re-creating a table to provide access to only certain data in the table, create a view that contains the columns and rows you want to make accessible.
Using a view is an ideal way to restrict some users to one portion of the data
and other users to a different portion of the data.
By using a view instead of building a table that must be populated from an existing table,
you do not increase the amount of data and you maintain security.
* Take advantage of indexes.
Remember that when you use a view,
you are still accessing the underlying tables and therefore the indexes on those tables.
If a table has a column that is indexed,
be sure to include that column in the WHERE clause of the view's SELECT statement.
Only if the column is part of the view
and is used in the WHERE clause can the index be used for selecting data.
For example,
if the Employee table has an index on the dept column
and this column is included in the view, the index can be used.
*Partition your data.
Views are especially useful in enabling you to partition data,
which can reduce the amount of time needed to rebuild indexes
and manage the virtual table by reducing the size of the individual components.
For example,
if an index rebuild on a single large table takes two hours,
you could partition the data into four smaller tables
for which the index rebuild time is much shorter.
You could then define a view that transparently combines the individual tables.
With large tables that store historical data, this technique can be quite useful.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook