SQL Server - Views VIII

In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table. Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table.

Benefits of Views

A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all of the data). Views can do the following:

• Restrict access to specific rows in a table
• Restrict access to specific columns in a table
• Join columns from multiple tables and present them as though they are part of a single table
• Present aggregate information (such as the results of the COUNT function)

Accessing Views

Any view that you create ends up being located under the "Views" folder of your database.
The following screenshot shows a number of views that are located within the "AdventureWorks2008" database:

Creating a View

You create a view by using the CREATE VIEW statement, followed by the SELECT statement.

CREATE VIEW ViewName AS
SELECT ...

Example:

CREATE VIEW "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))

Modifing a View

You can modify an existing view by using using ALTER instead or CREATE.
Example:

ALTER VIEW "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))

You can also right click on the view and select "Design".

Running a View

You run a view by using a SELECT statement.

SELECT TOP 1000 *  
FROM [AdventureWorks2008].[Sales].[vIndividualCustomer]

You can also right-click on the view and select "Select Top 1000 Rows".

Running the above view results in this:
As you can see, it looks just like you've selected rows from a table. The difference is that, each column could potentially be coming from a different table.