LearnNowOnline

Blog

Introduction to Views

Excerpt by Don Kiely | July 24, 2013

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 and servers.

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 viewsviews 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 function. 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 about 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 advantages include:

• 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.

This post is an excerpt from the online courseware for our SQL 2012 Developer: Views course written by expert Don Kiely.