SQL 2005+

SQL Server Views

by Richard Carr, published at http://www.blackwasp.co.uk/SQLViews.aspx

The twenty-first part of the SQL Server Programming Fundamentals tutorial examines the use of views. A view is a predefined SELECT statement that can be used as a virtual table, hiding complexities such as joins and filters from the consumers of the view.

What is a View?

A view is a named, stored query within a database. The query is a standard SELECT statement that may contain inner or outer joins, filters, aliases and other items that you would find in a query, including some that we will consider later in this tutorial. The view is presented as a virtual table that itself can be queried with additional joins, WHERE clauses, etc. Although the view can be queried as if it were a table, no data is stored in the view itself. The information is held in the underlying tables and accessed when the view is used.

Views are generally used to simplify access to a complex schema. For example, in an ERP system, a user may want to obtain the data required to generate a customer report. This report may include general customer information from the Accounts table. For financial data, this may be combined with aged debt information from the Debt table, outstanding orders from the Orders table and other information from the Quotes and Invoices tables. For a non-technical user, correctly linking all of these tables could be problematic. This can easily be solved by providing a view that presents the joined tables as a single source of data.

A second common use for a view is to hide information from users. If a table includes a column containing sensitive data, a view of the table can be created that omits the column. The security features of SQL Server can then allow access to the view but prevent direct reading of the table according to the user's privileges. You can also hide information by using a WHERE clause so that only selected rows are accessible.

View Limitations

Views provide a useful manner in which to control and simplify access to the database tables. However, there are some limitations to be considered. Firstly, although a view appears to operate in the same manner as a table, it is not always possible to insert data into a view or update information. Where the view retrieves data from a single table and where all of the table's columns that are not present in the view are nullable or have default values, inserting is possible. When a view joins several tables, updating is more limited.

A second problem with views occurs because they hide the complexity of the underlying schema. Where you have views that are based upon joined tables a user may decide to execute a query that joins several views. If these views each read from the same tables, the tables may be included in the query twice or more. This can severely impact the performance of the query in comparison to reading from the tables directly.

Adding a View to a Database

In this article we will add three views to the JoBS tutorial database. This database has been constructed and populated with data throughout the course of the tutorial. If you do not have a working copy of the database, download the script from the top of this page and execute it to create a database.

Adding a View Using SQL Server Management Studio

Views can be added to a database using the SQL Server Management Studio graphical user interface tools. The utility includes a view designer that allows tables to be selected and joined using drag and drop editing. You can also select the columns for the view and add aliases, filters and aggregate functions. The designer is beyond the scope of this article. Instead, we will concentrate on creating views using Transact-SQL (T-SQL).

Adding a View Using Transact-SQL

Views can be added to a database using T-SQL with the CREATE VIEW command. The basic syntax of the command is as follows:

The view-name in the above syntax is replaced with the unique name for the new view. The query that is to be used as the basis for the view should be used in place of select-statement.

Creating a Simple View

For our first example, we will create a view against the JoBS database. This view will read data from a single table but hide one of the columns. In this case, we will present the part number and part name for each row in the Parts table. The unit costs for the parts will not be available. Although this is not necessarily sensitive data, it shows how such information can be hidden.

To create the view, execute the following statement:

CREATE VIEW PartList AS
SELECT PartNumber, PartName FROM Parts

You can now query the view using the view name instead of a table. Note that the UnitCost column is not returned.

SELECT * FROM PartList

The view can be used in place of a table in more complex T-SQL statements, such as those containing joins. In the query below, the UsedParts table and the new PartList view are combined using an inner join.

SELECT
    U.JobId,
    P.PartNumber,
    P.PartName
FROM
    UsedParts U
INNER JOIN
    PartList P
ON
    U.PartNumber = P.PartNumber