BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Views

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.

Creating a Filtering View

In the second example below, we will create a view that retrieves filtered information from a table. In this case, the view returns information from the Contracts table. This table contains contracts that are renewed annually and individual payments for single jobs carried out without a contract. The view will filter the data to only return information that relates to single jobs. These are identified in the table as those rows without a specified renewal date.

CREATE VIEW SingleJobs AS
SELECT
    ContractNumber,
    CustomerAddressId,
    ContractValue
FROM
    Contracts
WHERE
    RenewalDate IS NULL

When you query this view, you will find that only a single row is returned. This is the one row that does not include a renewal date.

Creating a View Containing Joins

The final example demonstrates the creation of a view that simplifies access to a complex schema. In this case, we will join six tables in the view, allowing users to query the combined information without understanding the data structures involved.

CREATE VIEW JobList AS
SELECT
    J.VisitDate,
    C.FirstName,
    C.LastName,
    C.BusinessName,
    CO.ContractValue,
    SJ.JobName,
    SJ.JobDescription,
    E.EngineerName,
    CA.TownOrCity
FROM
    Jobs J
INNER JOIN
    StandardJobs SJ
ON
    J.StandardJobId = SJ.StandardJobId
INNER JOIN
    Contracts CO
ON
    J.ContractNumber = CO.ContractNumber
INNER JOIN
    CustomerAddresses CA
ON
    CO.CustomerAddressId = CA.AddressId
INNER JOIN
    Customers C
ON
    CA.CustomerNumber = C.CustomerNumber
LEFT JOIN
    Engineers E
ON
    J.EngineerId = E.EngineerId

Modifying an Existing View

Existing views can be modified using the ALTER VIEW statement, which has a similar syntax to the CREATE VIEW command. For example, you could modify the filtering view above to remove the customer address identifier using the following command:

ALTER VIEW SingleJobs AS
SELECT
    ContractNumber,
    ContractValue
FROM
    Contracts
WHERE
    RenewalDate IS NULL

Deleting Views

If a view is no longer required, it can be permanently removed from the database using the DROP VIEW command. For example, you could drop the SingleJobs view as follows:

DROP VIEW SingleJobs

Schema Binding

When you create a view, it is dependent upon the tables, columns, views and any other objects that are used within the query. If one of these items is dropped from the database, the view will be broken. To avoid this problem, you can create a view using the schema binding option. This links the view to the underlying data sources, preventing modifications that would break the view.

Schema binding is also required when using indexed views. These are views that are indexed to improve performance and will be described in the next article in this tutorial.

To add the schema binding option, the WITH SCHEMABINDING clause is added to the CREATE VIEW or ALTER VIEW statement. For example, to recreate the SingleJobs view you could use the following command:

CREATE VIEW SingleJobs WITH SCHEMABINDING AS
SELECT
    ContractNumber,
    ContractValue
FROM
    dbo.Contracts
WHERE
    RenewalDate IS NULL

NB: The table name is specified in a two part format for views with schema binding. The first part is the schema name. In the JoBS database there is only one schema, named "dbo".

27 January 2009