SQL Server 2005 - What is a View?

I explained that a couple of times lately so I decided to write a detailed description of a View in SQL Server 2005. Here we go:

There will be times when you want to group together data from more than one table, or
perhaps only allow users to see specific information from a particular table, where some of the
columns may contain sensitive or even irrelevant data. A view can take one or more columns
from one or more tables and present this information to a user, without the user accessing the
actual underlying tables. A view protects the data layer while allowing access to the data. All of
these scenarios can be seen as the basis and reason for building a view rather than another
method of data extraction. If you are familiar with MS Access, views are similar to Access queries.
Because a view represents data as if it was another table, a virtual table in fact, it is also possible to
create a view of a view.

Let’s take a look at how a view works. For example we have a customer table that holds
information about customers such as their first name, last name, account number, and
balances. There will be times when you want your users to have access to only the first and last
names, but not to the other sensitive data. This is where a view comes into play. You would
create a view that returns only a customer’s first and last name but no other information.
Creating a view can give a user enough information to satisfy a query he or she may have
about data within a database without that user having to know any T-SQL commands. A view
actually stores the query that creates it, and when you execute the view, the underlying query
is the code that is being executed. The underlying code can be as complex as required, therefore
leaving the end user with a simple SELECT * command to run with perhaps a small amount
of filtering via a simple WHERE statement.

From a view, in addition to retrieving data, you can also modify the data that is being
displayed, delete data, and in some situations insert new data. There are several rules and limitations
for deleting, modifying, and inserting data from multitable views, some of which will be
covered in the “Indexing a View� section later in the chapter.

However, a view is not a tool for processing data using T-SQL commands, like a stored
procedure is. A view is only able to hold one query at a time. Therefore, a view is more like a
query than a stored procedure. Just as with a stored procedure or a query within a Query Editor
pane, you can include tables from databases that are running on different servers. Providing
the user ID has the necessary security credentials, it is possible to include tables from several
databases.

So to summarize, a view is a virtual table created by a stored SQL statement that can span
multiple tables. Views can be used as a method of security within your database, and they
provide a simpler front end to a user querying the data.

Hope that helps..