You can use a SQL SELECT statement to read data from one or more tables in
a relational database. This is such a common operation that SQL provides a
way to save a SELECT statement as a persistent database object, called a view.
A view is simply a saved SELECT statement, which you can also think of as a
virtual table because the view object is used like a table. The view does not
create a copy of the underlying data. Instead, when you use a view SQL Server
dynamically reads that data from the underlying tables. A view can select data
from a single table or multiple tables, and can even span multiple databases
You can query views the same way you query tables – you SELECT from a
view and get a result back. You can also perform updates against views,
although with some limitations that you’ll learn about later in this chapter. You
can also create nested viewsviews that contain other views.
A view is not a stored procedure, even though there are some similarities. Both
can return a result set, but a view has no parameters and there is no procedural
logic, just a SELECT statement, although the statement can be as complicated
as you care to make it. Nor does a view have all the features of a user-defined
For a feature that is so simple in concept, views pack a lot of power. Let’s dive
into them and see how they can provide a handy way to access data. You’ll
also learn abou
t their limitations, to help you decide when it is better to use
SELECT statements directly. Let’s start with a discussion about the advantages
of views and quickly look at the security issues of views.
Advantages of Views
You can use views to provide data to client applications. Some of the
- You can give Columns in views friendly names to simplify databasestructures so that users can easily query them.
- You can use views to customize data with different filters and sorts for different users or different applications.
- The end user or client application doesn’t have to know anything about the complexity, join syntax, or aggregates in the query underlying the view.
- You can omit unnecessary or sensitive data from the view. In other words, you don’t have to include all the columns in the view results from the tables that comprise the view’s source data.
- Views can provide a backward compatible interface to emulate a table that no longer exists or that has had its schema modified.
- You can export data to another application using a view.
- You can use views to partition data based on whatever criteria you specify.>
A view is the sort of database object that, once you understand how they work
and the difference between them and SELECT statements, you’ll start finding
a wide variety of ways to put views to use.
Views and Security
One of the major reasons to use a view instead of executing SELECT
statements directly is to avoid granting permissions to users on the underlying
tables and other database objects. You can grant a user or role permission on a
view alone, thereby limiting a user’s access to the raw data. As long as the
owner of the view and the underlying tables are the same principal, the user
will have access to the data even without explicit rights to the underlying data.
Another way to describe this is that you can revoke all permissions to tables
and grant permissions on views that select only certain columns or rows from
those tables. Users will then be able to work with the views based on the
permissions granted to the views. This enables more granular control over
exposure of your data, letting you limit data modification so that it conforms to
the view criteria.
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.