Views: Limit Access to Data

You can use views to focus, simplify, and customize each user’s perception of the tables in a particular database. Views also provide a security mechanism by allowing users access only to the data they require.

A view is a named select statement that is stored in a database as an object. A view allows you to display a subset of rows or columns in one or more tables. Use a view by invoking its name in Transact-SQL statements.

A view is an alternative way of looking at the data in one or more tables. For example, suppose you are working on a project that is specific to the state of Utah. You can create a view that lists only the authors who live in Utah:

create view authors_ut
as select * from authors
where state = "UT"

To display the authors_ut view, enter:

select * from authors_ut

When the authors who live in Utah are added to or removed from the authors table, the authors_ut view reflects the updated authors table.

A view is derived from one or more real tables for which the data is physically stored in the database. The tables from which a view is derived are called its base tables or underlying tables. A view can also be derived from another view.

The definition of a view, in terms of the base tables from which it is derived, is stored in the database. No separate copies of data are associated with this stored definition. The data that you view is stored in the underlying tables.

A view looks exactly like any other database table. You can display it and operate on it almost exactly as you can any other table. There are no restrictions on querying through views and fewer than usual on modifying them.

When you modify the data in a view, you are actually changing the data in the underlying base tables. Conversely, changes to data in the underlying base tables are automatically reflected in the views that are derived from them.