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:
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