Database Reference
In-Depth Information
Finally, you can create the view with the
CHECK OPTION
parameter. When this option is specified, SQL Server
checks if the data, inserted or updated through the view, conforms to criteria set in the view select statement.
It guarantees that the rows will be visible through the view after the transaction is committed. For example,
look at the table and view defined in Listing 9-21.
Listing 9-21.
CHECK OPTION: Table and View creation
create table dbo.Numbers(Number int)
go
create view dbo.PositiveNumbers(Number)
as
select Number
from dbo.Numbers
where Number > 0
with check option
go
Either of the statements shown in Listing 9-22 would fail because they violate the criteria
Number > 0
specified in
the view select.
Listing 9-22.
CHECK OPTION: Insert statements
insert into dbo.PositiveNumbers(Number) values(-1)
update dbo.PositiveNumbers set Number = -1 where Number = 1
You should consider creating the view with
CHECK OPTION
when it is used to prevent access to a subset of the data
and client applications update the data through the view. Client applications would not be able to modify the data
outside of the allowed scope.
Summary
Views are a powerful and useful tool that can help in several different situations. Regular views can provide a
layer of abstraction from both the security and implementation standpoints. Indexed views can help with system
optimization, and they reduce the number of joins and aggregations that need to be performed.
As with other SQL Server objects, they come at a cost. Regular views can negatively affect performance by
introducing unnecessary joins. Indexed views introduce overhead during data modifications, and you need to
maintain their indexes in a manner similar to those defined on regular tables. You need to keep these factors in mind
when designing the views in systems.
Views are generally better suited to read data. Updating data through views is a questionable practice.
Using
INSERT OF
triggers is usually slower than directly updating the underlying tables. Without triggers, there
are restrictions that you have to follow to make views updatable. This could lead to side effects and break client
applications when you change the implementation of the views.
As with the other database objects, you need to consider pros and cons of views, especially when you design the
dedicated data access tier. Another option you have at your disposal is using stored procedures. Even though views
are generally simpler to use in client applications, you can add another filter predicate on the client side, for example,
without changing anything in the view definition, stored procedures provide more flexibility and control over
implementation during the development and optimization stages.
■
we will discuss implementation of the data access tier in greater detail in Chapter 16, “system
Design Considerations.”
Note
