Transcript Chapter 7

Module 7:
Implementing Views
Overview
Introduction to Views
Creating and Managing Views
Optimizing Performance by Using Views
Lesson 1: Introduction to Views
What Is a View?
Types of Views
Advantages of Views
What Is a View?
Employee (table)
EmployeeID
LastName
FirstName
Title
…
287
Mensa-Annan
Tete
Mr.
…
288
Abbas
Syed
Mr.
…
289
Valdez
Rachel
NULL
…
vEmployee (view)
LastName
FirstName
Mensa-Annan
Tete
Abbas
Syed
Valdez
Rachel
Types of Views
Standard views
 Combine data from one or more base tables (or views)
into a new virtual table
Indexed views
 Materialize (persist) the view through the creation of
a unique clustered index on the view
Partitioned views
 Join horizontally partitioned data from one or more base
tables across one or more servers
Advantages of Views
Focus the data for a user
Mask database complexity
Simplify management of user permissions
Improve performance
Organize data for export to other applications
Lesson 2: Creating and Managing Views
Syntax for Creating Views
Demonstration: Creating a View
Syntax for Altering and Dropping Views
How Ownership Chains Affect Views
Sources of Information About Views
View Encryption
Considerations for Modifying Data in a View
Practice: Creating a View
Syntax for Creating Views
Use CREATE VIEW Transact-SQL statement:
CREATE VIEW [ schema_name.] view_name [ (column [ ,...n ] ) ]
[WITH [ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
Restrictions:
 Cannot nest more than 32 levels deep
 Cannot contain more than 1,024 columns
 Cannot use COMPUTE, COMPUTE BY, or INTO
 Cannot use ORDER BY without TOP
Demonstration: Creating a View
In this demonstration, you will see how to:
Create a view
Query a view
Generate a script for a view
Syntax for Altering and Dropping Views
Alter by using the ALTER VIEW Transact-SQL statement:
ALTER VIEW [ schema_name.]view_name [ (column [ ,...n ] ) ]
[WITH [ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
Drop by using the DROP VIEW Transact-SQL statement:
DROP VIEW [ schema_name.]view_name [ ...,n ] [ ; ]
How Ownership Chains Affect Views
Access view
1
2
View Owner: Mary
3
View Owner: Mary
4
Table Owner: Tim
User: John
Dependency
Dependency
Sources of Information About Views
SQL Server Management Studio
Source
Information
List of views in database
Object Explorer
Access to columns, triggers, indexes, and
statistics defined on views
View Properties dialog box
Properties of individual views
Transact-SQL
Source
Information
sys.views
List of views in database
sp_helptext
Definition of non-encrypted views
sys.sql_dependencies
Objects (including views) that depend on
other objects
View Encryption
Use the WITH ENCRYPTION option on CREATE VIEW
Transact-SQL statement
 Encrypts view definition in sys.syscomments table
 Protects view creation logic
CREATE VIEW [HumanResources].[vEmployee]
WITH ENCRYPTION AS SELECT
e.[EmployeeID],c.[Title],c.[FirstName],c.[MiddleName]
,c.[LastName],c.[Suffix],e.[Title] AS [JobTitle]
,c.[Phone],c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
Use WITH ENCRYPTION on ALTER VIEW statements to retain encryption
Considerations for Modifying Data in a View
Views do not maintain a separate copy of data (indexed
views are an exception)
Updates to views modify base tables
Restrictions:
 Cannot affect more than one base table
 Cannot modify columns derived from aggregate
functions or calculations
 Cannot modify columns affected by GROUP BY,
HAVING, or DISTINCT clauses
Updates to views are restricted by using the WITH
CHECK OPTION
Practice: Creating a View
In this practice, you will create a view
Lesson 3: Optimizing Performance by Using Views
Performance Considerations for Views
What Is an Indexed View?
What Is a Partitioned View?
Performance Considerations for Views
Views introduce performance overhead because views
are resolved dynamically
Nested views introduce risk of performance problems
 Review definition of unencrypted nested views
 Use SQL Server Profiler to review performance
Indexed views and partitioned views can
improve performance
What Is an Indexed View?
A view with a unique clustered index
 Materializes view, improving performance
 Allows query optimizer to use view in query resolution
Use when:
 Performance gains outweigh maintenance overhead
 Underlying data is modified infrequently
 Queries perform a significant number of joins
and aggregations
CREATE UNIQUE CLUSTERED INDEX
[IX_vStateProvinceCountryRegion] ON
[Person].[vStateProvinceCountryRegion]
( [StateProvinceID] ASC, [CountryRegionCode] ASC)
What Is a Partitioned View?
Joins horizontally partitioned data from a set of tables
across one or more servers
SQLServerNorth.Sales.Sale
vSales
SQLServerSouth.Sales.Sale
CREATE VIEW vSales AS
SELECT *
FROM SQLServerNorth.Sales.Sale
UNION ALL
SELECT *
FROM SQLServerSouth.Sales.Sale
Lab: Implementing Views
Exercise 1: Creating Views
Exercise 2: Creating Indexed Views
Exercise 3: Creating Partitioned Views