Microsoft® SQL Server® 2012 Bible

284

Part II: Building Databases and Working with Data

Alternatives to Views

Besides views, SQL Server offers several technologies to build an abstraction layer around the data.

Stored procedures are generally a fi rst choice when exposing any data to the outside world. User-

defi ned functions offer several benefi ts, and inline table-valued user-defi ned functions are similar to

views but with parameters. Chapter 17, “Developing Stored Procedures,” and Chapter 18 discuss the

aforementioned topics at length.

If you use views to support ad hoc queries, as I suggest you do, you may also want to explore provid-

ing Analysis Services cubes for those users who need to perform complex explorations of the data.

Cubes pre-aggregate, or summarize, the data along multiple dimensions. The user may then browse

the cube and compare the different data dimensions. For the developer, providing one cube can often

eliminate several queries or reports.

Chapter 53, “Building Multidimensional Cubes with Analysis Services and MDX,” explains how to

create cubes.

Locking Down the View

Views are designed to control access to data. Several options protect the data or the view.

The WITH CHECK OPTION causes the WHERE clause of the view to check the data being

inserted or updated through the view in addition to the data being retrieved. In a sense, it

makes the WHERE clause a two-way restriction.

The WITH CHECK OPTION is useful when the view should limit inserts and updates with

the same restrictions applied to the WHERE clause.

Unchecked Data

To understand the need for the WITH CHECK OPTION, you must fi rst understand how

views function without the CHECK OPTION. The following view generates a list of Product

SubCategories for the Bikes Product Category:

USE AdventureWorks2008R2

GO

CREATE view vComponentsProductSubCats

AS

SELECT

ProductCategoryID,

Name ProductSubCategory

FROM Production.ProductSubcategory

WHERE ProductCategoryID = 1;

GO

SELECT ProductCategoryID, ProductSubCategory FROM

c11.indd 284c11.indd 284 7/30/2012 4:41:11 PM7/30/2012 4:41:11 PM

http://www.it-ebooks.info