Databases Reference
In-Depth Information
You also have the ALTER VIEW and DROP VIEW commands available to
manage existing views. ALTER VIEW lets you modify the view definition, which
includes letting you specify a different SELECT statement for the view. Use DROP
VIEW to delete existing views.
Using Views
Views are most often used to provide restricted access to the underlying base
objects. In SQL Server, views can also simplify security management because you
can give users (or an application) access to a view without granting them access
to the underlying objects. This helps prevent unauthorized access or modifica-
tions to the base data.
Let's look at an example using the CUSTOMER table in Figure 8-9. There is
a requirement for operations that are performed on a city-by-city basis.
You've been asked to create a view based on CUSTOMER with the data fil-
tered by city. Here's the statement you might use to create the view for New York
customers:
CREATE VIEW v_ny_cust AS SELECT * FROM CUSTOMERS
WHERE HQCITY = 'New York'
To retrieve data from this view, you could use
SELECT * FROM v_ny_cust
Here's a view based on a join that lets you retrieve customer employee infor-
mation with the customer name rather than CUSTNUM value:
CREATE VIEW v_custemp_name AS
SELECT CUSTNAME, EMPNUM, EMPNAME
FROM CUSTOMER C, [CUSTOMER EMPLOYEE] E
WHERE C.CUSTNUM=E.CUSTNUM
Figure 8-9
CUSTOMER table
CUSTNUM
CUSTNAME
SPNUM
HQCITY
0121
0839
0933
1047
1525
1700
1826
2198
2267
Main St. Hardware
Jane's Stores
ABC Home Stores
Acme Hardware Store
Fred's Tool Stores
XYZ Stores
City Hardware
Western Hardware
Central Stores
137
186
137
137
361
361
137
204
186
New York
Chicago
Los Angeles
Los Angeles
Atlanta
Washington
New York
New York
New York
CUSTOMER table.
