ptg
834
CHAPTER 26 Implementing Data Integrity
tmstmp timestamp NOT NULL,
password char(13) NOT NULL DEFAULT ‘defaultpw’,
Shortdesc VARCHAR(50) NULL)
The table in this example has a unique characteristic: each column has some sort of
default value associated with it. One column has a default of NULL because it is nullable.
The IDENTITY and TIMESTAMP columns automatically generate values because of their data
type, and the password column has an explicit default definition. In this scenario, you can
supply the keywords DEFAULT VALUES in the INSERT statement to insert a row of data, as
shown in the following example:
INSERT test_default DEFAULT VALUES
select * from test_default
/* results from previous select statement
id tmstmp password Shortdesc
—————- ————————— ——————- ————————————-
1 0x00000000000007D1 defaultpw NULL
*/
You can see from the results of the SELECT statement in this example that a row was
inserted in the new table, and this row includes default values for all the columns. If you
want to supply values for some of the columns and allow the defaults to be used for other
columns, you can simply exclude the columns with defaults from the column listing in
the INSERT statement. The following example demonstrates how to do this:
INSERT test_default (ShortDesc)
VALUES(‘test default insertion’)
SELECT * FROM test_default
where ShortDesc = ‘test default insertion’
/* results from previous select statement
id tmstmp password Shortdesc
—- ————————— ————— ———————————-

2 0x00000000000007D2 defaultpw test default insertion
*/
The DEFAULT keyword can also be listed explicitly in the VALUE listing of the INSERT state-
ment, as shown in the following example:
INSERT test_default (tmstmp, password, ShortDesc)
VALUES(DEFAULT, DEFAULT, DEFAULT)
SELECT * FROM test_default where id = @@identity
/*
(1 row(s) affected)
id tmstmp password Shortdesc
—- ————————— ——————- —————
3 0x00000000000007D5 defaultpw NULL
*/
Download from www.wowebook.com
ptg
835
Defaults
26
All the examples so far have dealt with INSERT statements, but there is one scenario in
which a default value can be applied with an UPDATE statement. This scenario is similar to
the preceding example and requires the use of the DEFAULT keyword. The following
example demonstrates the use of the DEFAULT keyword in an UPDATE statement:
UPDATE top (1) test_default
SET PASSWORD = DEFAULT
GO
SELECT top 1 * from test_default
/*
id tmstmp password Shortdesc
—————- ————————— ——————- —————-
1 0x00000000000007DE defaultpw NULL

*/
Keep in mind that default values are not used for updates unless the DEFAULT keyword is
explicitly referenced in the SET clause of the UPDATE statement.
Restrictions on Defaults
When creating defaults, you need to keep in mind the following restrictions:
. A default cannot be created on columns that have been defined with TIMESTAMP,
IDENTITY, or ROWGUIDCOL properties.
. Only one default can be assigned to a given column. This restriction applies to both
declarative and bound defaults.
. Only one default can exist per column.
. The default value must be compatible with the data type of the column.
. A default that is bound cannot be dropped if the default is currently bound to a
column. The default must be unbound from the column first.
. The expression in a default cannot include the names of any columns or other data-
base objects.
There are also some considerations related to the interaction of rules, defaults, and
constraints:
. If a column has both a rule and default, the default is not inserted if it violates the
rules.
. If a default value violates a CHECK constraint, the default is not inserted. Ultimately,
all the rules, defaults, and constraints that are active are validated. If the change to
the data violates any of them, it is rejected.
Download from www.wowebook.com
ptg
836
CHAPTER 26 Implementing Data Integrity
Summary
This chapter covers the basic tools you can use to ensure the integrity of the data in a
database. The integrity of data is directly related to its value; remember the concept of
“garbage in, garbage out.” If you take the time to implement the constraints and other

methods discussed in this chapter, you provide a solid foundation for the storage of data
and avoid the headaches related to dealing with “garbage” data.
Chapter 27, “Creating and Managing Views in SQL Server,” discusses a means for virtu-
ally accessing the data in tables. Virtual tables, or views, allow you to selectively choose
the data elements on one or more tables that you want to present as a single window
into your data.
Download from www.wowebook.com
ptg
CHAPTER 27
Creating and Managing
Views in SQL Server
IN THIS CHAPTER
. What’s New in Creating and
Managing Views
. Definition of Views
. Using Views
. Creating Views
. Managing Views
. Data Modifications and Views
. Partitioned Views
. Indexed Views
Views offer a window into your data that does not require
physical storage. They are essentially virtual tables that are
defined by a SELECT statement. This chapter describes the
benefits and advantages of these powerful database objects.
What’s New in Creating and
Managing Views
Much of the core functionality associated with standard
views has remained unchanged in SQL Server 2008.
However, some storage enhancements have been added to

SQL Server 2008 that can be used with views. These storage
enhancements include disk storage compression in both
row and page format. It is available on indexed views and
can be used on tables and indexes as well. This data
compression is not covered in this chapter but is covered in
detail in Chapter 34, “Data Structures, Indexes, and
Performance.”
Definition of Views
Views are a logical way of viewing data in the underlying
physical tables. They are tied to a SELECT statement that
retrieves data from one or more tables or views in the same
database or a different database. In most cases, there is no
physical storage of data associated with the view, and the
SELECT that is associated with the view is run dynamically
whenever the view is referenced.
Download from www.wowebook.com
ptg
838
CHAPTER 27 Creating and Managing Views in SQL Server
The following T-SQL statement can be used to create a simple view in the
Adventureworks2008 database:
CREATE VIEW [dbo].[vw_CustomerAddress]
AS
SELECT Sales.Customer.CustomerID, Sales.Customer.AccountNumber,
Person.Address.AddressLine1,
Person.Address.StateProvinceID, Person.Address.City,
Person.Address.PostalCode
FROM Sales.Customer
INNER JOIN Person.Person
ON Sales.Customer.PersonID = Person.Person.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress
ON Person.Person.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID
INNER JOIN Person.Address ON Person.BusinessEntityAddress.AddressID =
Person.Address.AddressID
The vw_CustomerAddress view in this example selects from four different tables in the
Adventureworks2008 database: Sales.Customer, Person.Person, Person.Business
EntityAddress, and Person.Address. After the view is created, it can be used in the FROM
clause of another SELECT statement. The following data retrieval example uses the newly
created view:
select c.AccountNumber, s.OrderDate, c.city , c.StateProvinceId
from vw_CustomerAddress c
INNER JOIN Sales.SalesOrderHeader s
ON c.CustomerID = s.CustomerID
WHERE StateProvinceId = 14
AND s.OrderDate = ‘9/21/01’
ORDER BY c.city
AccountNumber OrderDate city StateProvinceId
AW00020060 2001-09-21 00:00:00.000 Runcorn 14
AW00011333 2001-09-21 00:00:00.000 Newcastle upon Tyne 14
You can see from the sample SELECT that the view is treated much like a table that is refer-
enced in a SELECT statement. The view can be joined to other tables, individual columns
from the view can be selected, and those columns can be included in the ORDER BY clause.
All the retrieval is done dynamically when the view is referenced, and the underlying
tables that are part of the view definition are implicitly accessed, without the need to
know the underlying structure of the view.
Download from www.wowebook.com
ptg
839
Using Views
27

Using Views
Views are useful in many scenarios. Some of the most common scenarios include the
following:
. Simplifying data manipulation
. Focusing on specific data
. Abstracting data
. Controlling access to data
Simplifying Data Manipulation
Views can be used to simplify data access. Common queries that utilize complex joins,
UNION queries, and more involved SQL can be defined as views. This minimizes the
amount of complex code that must be written or rewritten and provides a simple way of
organizing your common data access.
SQL Server 2008 comes with a set of system views that demonstrate the views’ capability
to mask complex queries and simplify data manipulation. These system views include
catalog views, information schema views, and compatibility views. In many cases, the
definition of these views is hidden, but some of them can be analyzed using the
sp_helptext system procedure. For example, sys.triggers, a catalog view defined in SQL
Server 2008, has the following definition associated with it:
CREATE VIEW sys.triggers AS
SELECT o.name,
object_id = o.id,
parent_class = o.pclass,
parent_class_desc = pc.name,
parent_id = o.pid,
type = o.type,
type_desc = n.name,
create_date = o.created,
modify_date = o.modified,
is_ms_shipped = sysconv(bit, o.status & 1), — OBJALL_MSSHIPPED
is_disabled = sysconv(bit, o.status & 256), — OBJTRG_DISABLED

is_not_for_replication = sysconv(bit, o.status & 512), — OBJTRG_NOTFORREPL
is_instead_of_trigger = sysconv(bit, o.status & 1024) — OBJTRG_INSTEADOF
FROM sys.sysschobjs o
LEFT JOIN sys.syspalnames n ON n.class = ‘OBTY’ AND n.value = o.type
LEFT JOIN sys.syspalvalues pc ON pc.class = ‘UNCL’ AND pc.value = o.pclass
WHERE o.type IN (‘TA’,’TR’) AND o.pclass <> 100
AND has_access(‘TR’, o.id, o.pid, o.nsclass) = 1
To select the relevant data from the sys.triggers view, you need only reference the
columns in the view that are of interest, and the complexity of the view is hidden. The
Download from www.wowebook.com
ptg
840
CHAPTER 27 Creating and Managing Views in SQL Server
following query demonstrates the simplicity of a SELECT statement against the
sys.triggers view:
select name, type, create_date
from sys.triggers
where name like ‘i%’
You can see from the sys.triggers example why the folks at Microsoft are big proponents
of views. Complex queries such as the sys.triggers view can be written and tested once,
and subsequent data retrieval can be accomplished by selecting from the view.
Focusing on Specific Data
Views allow users or developers to focus on the specific data elements they need to work
with. Tables that contain hundreds of columns or columns that have limited value for the
end user can be filtered with a view such that only the relevant data elements are
returned.
The HumanResources.vEmployee view in the Adventureworks2008 database is a good
example of a view that focuses on specific data and simplifies data access. The view defini-
tion follows:
ALTER VIEW [vEmployee]

AS
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[BusinessEntityAddress] bea
Download from www.wowebook.com
ptg
841
Using Views
27
ON bea.[BusinessEntityID] = e.[BusinessEntityID]

INNER JOIN [Person].[Address] a
ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.BusinessEntityID = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] =
ea.[BusinessEntityID]
The HumanResources.vEmployee view filters out much of the data that is sensitive or super-
fluous when gathering the basic information about an employee.
Abstracting Data
Data abstraction, in its simplest form, isolates the client code from changes to the under-
lying structure. A view can be used to implement data abstraction within your database
schema. If, for example, you have client code that will retrieve data from a database table
that is likely to change, you can implement a view that retrieves data from the underlying
table. The client code will then reference the view and never access the underlying table
directly. If the underlying tables change or the source of the data for the view changes,
these changes can be isolated from the referencing client code.
To demonstrate this scenario, let’s look at the following SELECT statement, which retrieves
data directly from the Sales.SalesOrderHeader table:
select TerritoryID, sum(TotalDue)
from Sales.SalesOrderHeader
group by TerritoryID
order by TerritoryID
The client code could certainly utilize this kind of query to retrieve the territory data. You
may find, however, that the data retrieval would be better placed within a view if the

summarized territory data were slated to be rolled up into an aggregate table at a later
time. In this scenario, a view like the following could be created initially:
CREATE VIEW vw_TerritoryOrders AS
select TerritoryID, sum(TotalDue) ‘TotalSales’
from Sales.SalesOrderHeader
group by TerritoryID
The client code that needs the territory data would then reference the
vw_TerritoryOrders view. If the source of the territory data changes and it is rolled up in
an aggregate table, the view can be changed to reflect the new source for the data, but the
Download from www.wowebook.com
ptg
842
CHAPTER 27 Creating and Managing Views in SQL Server
client code remains unchanged. The following example alters the vw_TerritoryOrders
view such that the source of the data is changed:
ALTER VIEW vw_TerritoryOrders AS
select TerritoryID, SalesYTD ‘TotalSales’
from Sales.SalesTerritory
Changing a single view in these types of scenarios can be much easier than changing the
client code that has direct references to the table. This type of abstraction also applies to
partitioned views, which are discussed later in this chapter.
Controlling Access to Data
Views can be used as a security mechanism to limit a user’s access to specific data. This
type of view security can be used to limit the columns that a user has access to or the
rows that the user has access to. A view that limits the accessible columns can be referred
to as vertical security,or column-level security. A view that restricts the rows that are
returned is referred to as horizontal security,or row-level security.
With vertical security, a view is created that contains only the data elements or columns
that you want to make visible. Columns that are sensitive in nature (for example,
payroll data) can be excluded from a view so that they are not seen when the user

selects from the view.
After the view is created, security can be granted on the view. If the owner of the objects
referenced in the view is the same as the owner of the view itself, the user who is granted
permission to the view does not need to have permission granted to the underlying
objects. Listing 27.1 gives an example of this scenario.
LISTING 27.1 Security with Views
USE adventureworks2008
go
CREATE LOGIN OwnerLogin WITH PASSWORD = ‘pw’
CREATE USER OwnerLogin FOR LOGIN OwnerLogin
EXEC sp_addrolemember N’db_owner’, N’OwnerLogin’
CREATE LOGIN NonOwnerLogin WITH PASSWORD = ‘pw’
CREATE USER NonOwnerLogin FOR LOGIN NonOwnerLogin
—Connect as the OwnerLogin at this point
Go
CREATE VIEW OwnerView as
select LoginID, JobTitle, BirthDate, Gender, HireDate, SalariedFlag
from HumanResources.Employee go
GRANT SELECT ON [dbo].[OwnerView] TO [NonOwnerLogin]
Download from www.wowebook.com
ptg
843
Using Views
27
—Connect as the NonOwnerLogin at this point
—The following select succeeds because the owner of the
—view that was granted permission is the same as the underlying
—table in the view
select * from OwnerView
—The following SELECT against the underlying table fails

—because the NonOwnerLogin does not have permission to
—select from the table. He can only select through the view
select * from HumanResources.Employee
Listing 27.1 outlines a scenario where one login creates a view that selects specific
columns from the HumanResources.Employee table. The Employee table is part of the
HumanResources schema, and it is owned by DBO. The view that is created is also owned by
DBO because the login (OwnerLogin) that created the view is a member of the db_owner
role. Ultimately, NonOwnerLogin is granted permission to the view. When the
NonOwnerLogin user connects to the database, that user can select rows from the view and
will see only the columns in the Employee table that have been selected in the view. If that
user tries to select rows directly from the underlying HumanResources.Employee table, a
permission-related error fires. Ownership chaining is the key to making this scenario work.
With ownership chaining, SQL Server automatically authorizes a user to access the under-
lying tables, views, or functions referenced in the view. This happens only if the view has
the same owner as the underlying objects and the user has been granted permission to the
view. If, however, you have various owners of the underlying objects that a view refer-
ences, permissions must be checked at each level. If access is denied at any level, access to
the view is denied. Ownership chaining was available in prior versions and is still avail-
able in SQL Server 2008 for backward compatibility.
Horizontal security can also be implemented with a view. With horizontal security, a WHERE
clause is included in the view’s SELECT statement to restrict the rows that are returned. The
following example demonstrates a simple view that utilizes horizontal security:
CREATE VIEW EmpViewHorizontal
as
select EmployeeID, BirthDate, Gender, HireDate, SalariedFlag
from HumanResources.Employee
where HireDate > ‘3/1/03’
—Sample SELECT results from the view:
LoginID BirthDate Gender HireDate SalariedFlag
adventure-works\syed0 1965-02-11 M 2003-04-15 1

adventure-works\lynn0 1961-04-18 F 2003-07-01 1
Download from www.wowebook.com