200 likes | 203 Views
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?. Types of Views. Standard views
E N D
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
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 ofa 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: • 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 CREATE VIEW [ schema_name.] view_name [ (column [ ,...n ] ) ] [WITH [ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA]] AS select_statement [ ; ][ WITH CHECK OPTION ]
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: • Drop by using the DROP VIEW Transact-SQL statement: ALTER VIEW [ schema_name.]view_name [ (column [ ,...n ] ) ] [WITH [ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA]] AS select_statement [ ; ][ WITH CHECK OPTION ] DROP VIEW [ schema_name.]view_name [ ...,n ] [ ; ]
How Ownership Chains Affect Views View Owner: Mary 2 Access view User: John 1 Dependency View Owner: Mary 3 Dependency Table Owner: Tim 4
Sources of Information About Views • SQL Server Management Studio • Transact-SQL
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 SELECTe.[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 canimprove 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 joinsand 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 SQLServerSouth.Sales.Sale CREATE VIEW vSales AS SELECT * FROM SQLServerNorth.Sales.Sale UNION ALLSELECT * FROM SQLServerSouth.Sales.Sale vSales
Lab: Implementing Views • Exercise 1: Creating Views • Exercise 2: Creating Indexed Views • Exercise 3: Creating Partitioned Views