SQLServerCh13

Chapter 13

How to work with views

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 1

Objectives
Applied
 Given a SELECT statement, create a new view based on the
statement.
 Given a SELECT statement, use the View Designer to create a
new view based on the statement.
 Use the View Designer to change the design of an existing view.
Knowledge
 Describe the use of views.
 Name the three SQL statements you use to work with views.
 Given a SELECT statement, determine whether it can be used as
the basis for a view.
 Given a SELECT statement, determine whether it can be used as
the basis for an updatable view.

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 2

Objectives (cont.)
 Describe the benefits provided by views.
 Describe the effects of the WITH SCHEMABINDING and WITH
ENCRYPTION clauses on a view.
 Describe the effect of the WITH CHECK OPTION clause on an
updatable view.
 Describe the use of SQL Server’s catalog views for getting
information from the system catalog.

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 3

A CREATE VIEW statement
CREATE VIEW VendorsMin AS
SELECT VendorName, VendorState, VendorPhone
FROM Vendors;

The virtual table that’s represented by the view

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 4

A SELECT statement that uses the view
SELECT * FROM VendorsMin
WHERE VendorState = 'CA'
ORDER BY VendorName;

The result set returned by the SELECT statement

(75 rows)

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 5

Some of the benefits provided by views
 Design independence
 Data security
 Flexibility
 Simplified queries
 Updatability

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 6

The data in a table of investors

(5 rows)

A view that restricts access to certain columns
CREATE VIEW InvestorsGeneral
AS
SELECT InvestorID, LastName, FirstName, Address,
City, State, ZipCode, Phone
FROM Investors;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 7

The data retrieved by the view

(5 rows)

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 8

The syntax of the CREATE VIEW statement
CREATE VIEW view_name [(column_name_1
[, column_name_2]...)]
[WITH
{ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
AS
select_statement
[WITH CHECK OPTION]

A view of vendors that have invoices
CREATE VIEW VendorShortList
AS
SELECT VendorName, VendorContactLName,
VendorContactFName, VendorPhone
FROM Vendors
WHERE VendorID IN (SELECT VendorID FROM Invoices);

The response from the system
Command(s) completed successfully.

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 9

A view that uses a join
CREATE VIEW VendorInvoices
AS
SELECT VendorName, InvoiceNumber, InvoiceDate,
InvoiceTotal
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 10

A view that uses TOP and ORDER BY clauses
CREATE VIEW TopVendors
AS
SELECT TOP 5 PERCENT VendorID, InvoiceTotal
FROM Invoices
ORDER BY InvoiceTotal DESC;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 11

A view that names all of its columns
in the CREATE VIEW clause
CREATE VIEW OutstandingInvoices
(InvoiceNumber, InvoiceDate, InvoiceTotal, BalanceDue)
AS
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
InvoiceTotal - PaymentTotal - CreditTotal
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 12

A view that names just the calculated column
in its SELECT clause
CREATE VIEW OutstandingInvoices
AS
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 13

A view that summarizes invoices by vendor
CREATE VIEW InvoiceSummary
AS
SELECT VendorName, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceSum
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorName;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 14

A view that uses the WITH SCHEMABINDING
option
CREATE VIEW VendorsDue
WITH SCHEMABINDING
AS
SELECT InvoiceDate AS Date, VendorName AS Name,
VendorContactFName + ' ' + VendorContactLName
AS Contact, InvoiceNumber AS Invoice,
InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue
FROM dbo.Vendors JOIN dbo.Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 15

Requirements for creating updatable views
 The select list can’t include a DISTINCT or TOP clause.
 The select list can’t include an aggregate function.
 The select list can’t include a calculated value.
 The SELECT statement can’t include a GROUP BY or HAVING
clause.
 The view can’t include the UNION operator.

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 16

An updatable view
CREATE VIEW InvoiceCredit
AS
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
PaymentTotal, CreditTotal
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

An UPDATE statement that updates the view
UPDATE InvoiceCredit
SET CreditTotal = CreditTotal + 200
WHERE InvoiceTotal – PaymentTotal – CreditTotal >= 200;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 17

A read-only view
CREATE VIEW OutstandingInvoices
AS
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 18

The syntax of the DROP VIEW statement
DROP VIEW view_name

The syntax of the ALTER VIEW statement
ALTER VIEW view_name [(column_name_1
[, column_name_2]...)]
[WITH
{ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
AS
select_statement
[WITH CHECK OPTION]

Note
 When you delete a view, any permissions that are assigned to the
view are also deleted.

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 19

A statement that creates a view
CREATE VIEW Vendors_SW
AS
SELECT *
FROM Vendors
WHERE VendorState IN ('CA','AZ','NV','NM');

A statement that modifies the view
ALTER VIEW Vendors_SW
AS
SELECT *
FROM Vendors
WHERE VendorState IN ('CA','AZ','NV','NM','UT','CO');

A statement that deletes the view
DROP VIEW Vendors_SW;

Murach's SQL Server, C13

© 2012, Mike Murach & Associates, Inc.

Slide 20