1 / 20

Enhancing Database Efficiency with Views: Creation, Management, and Performance Optimization

Learn how to create and manage views, optimize performance, and implement indexed and partitioned views for better database management. Discover the advantages of views, syntax for creating and modifying views, and considerations for optimizing performance.

wsibley
Download Presentation

Enhancing Database Efficiency with Views: Creation, Management, and Performance Optimization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 7:Implementing Views

  2. Overview • Introduction to Views • Creating and Managing Views • Optimizing Performance by Using Views

  3. Lesson 1: Introduction to Views • What Is a View? • Types of Views • Advantages of Views

  4. What Is a View?

  5. 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

  6. 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

  7. 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

  8. 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 ]

  9. Demonstration: Creating a View In this demonstration, you will see how to: • Create a view • Query a view • Generate a script for a view

  10. 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 ] [ ; ]

  11. How Ownership Chains Affect Views View Owner: Mary 2 Access view User: John 1 Dependency View Owner: Mary 3 Dependency Table Owner: Tim 4

  12. Sources of Information About Views • SQL Server Management Studio • Transact-SQL

  13. 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

  14. 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

  15. Practice: Creating a View In this practice, you will create a view

  16. Lesson 3: Optimizing Performance by Using Views • Performance Considerations for Views • What Is an Indexed View? • What Is a Partitioned View?

  17. 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

  18. 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)

  19. 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

  20. Lab: Implementing Views • Exercise 1: Creating Views • Exercise 2: Creating Indexed Views • Exercise 3: Creating Partitioned Views

More Related