9767406 - implementing views

Module 6

Implementing Views

Contents:

Lesson 1: Introduction to Views 6-2

Lesson 2: Creating and Managing Views 6-8

Lesson 3: Optimizing Performance by Using Views 6-22

Lab: Implementing Views 6-28

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links are provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

©2006 Microsoft Corporation. All rights reserved.

Microsoft, JScript, MSDN, Outlook, PowerPoint, Visual Basic, Visual C#, Visual C++, Visual FoxPro, Windows, and Windows Server are either registered tradmarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Module 6: Implementing Views 6–1

**************************************** Illegal for non-trainer use ***************************************

Module objectives After completing this module, students will be able to:

■ Describe the types and uses of views.

■ Create and manage views.

■ Optimize the performance of views.

Introduction Most database implementations include views to provide a convenient way to access data through a predefined query. By learning how to create views, you can make it easier for users to access the data they need and can improve the usability and performance of your database.

This module introduces views and summarizes the advantages that they provide. It describes how to create views by using Microsoft® SQL Server™ Management Studio and Transact-SQL, the options available, and how to find information about views. The module then addresses restrictions on modifying data through views and how views can improve database performance.

6–2 Module 6: Implementing Views

Lesson 1: Introduction to Views


Lesson objectives After completing this lesson, students will be able to:

■ Explain the uses of views.

■ Describe the types of views.

■ Describe the advantages of views.

Introduction This lesson defines what a view is, describes the uses of views, and summarizes the different types of views available in Microsoft SQL Server 2005. The lesson then describes the advantages of using views.


What Is a View?


Definition A view is a virtual table whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. Unless it is indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced. The tables queried in a view are called base tables.

Common examples of views are:

■ A subset of rows or columns of a base table

■ A union of two or more base tables

■ A join of two or more base tables

■ A statistical summary of a base table

■ A subset of another view, or some combination of views and base tables

Uses of views Views are commonly used to:

■ Let users focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary or sensitive data can be left out of the view.

■ Simplify how users work with data. You can define frequently used joins, projections, UNION queries, and SELECT queries as views so that users do not have to specify all the conditions and qualifications every time an additional operation is performed on that data.

■ Improve security by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.

■ Provide backward compatibility by defining a view to emulate a table that used to exist but whose schema has changed.


■ Define the set of data that a user can export from and import to SQL Server.

■ Provide a consolidated representation of partitioned data—that is, similar data that is stored across multiple tables.

For More Information For more information about views, see “Understanding Views” in SQL Server Books Online.


Types of Views


Introduction In SQL Server 2005, you can create three types of views:

■ Standard views

■ Indexed views

■ Partitioned views

Standard views A standard view combines data from one or more base tables into a new virtual table. Only the definition of a standard view is stored, not the rows of the view. Whenever the view is referenced, the Database Engine creates the data for the view dynamically. Standard views are the type of view that you will use most frequently.

For More Information For more information about standard views, see “Scenarios for Using Views” in SQL Server Books Online.

Indexed views An indexed view is a view that has been materialized, meaning that it has been computed and stored. You index a view by creating a unique clustered index on the view. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows, but they are not well suited for base tables that are frequently updated.

For More Information For more information about indexed views, see “Designing Indexed Views” in SQL Server Books Online.

Partitioned views A partitioned view joins horizontally partitioned data from one or more base tables across one or more servers. This makes similar data from multiple base tables appear as if it is from one table. A view that joins member tables on the same instance of SQL Server is called a local partitioned view. When a view joins data from tables across servers, it is called a distributed partitioned view.


Note Local partitioned views are included in SQL Server 2005 for backward compatibility only and are in the process of being deprecated. The preferred method for partitioning data locally is to use partitioned tables. For more information, see “Partitioned Tables and Indexes” in SQL Server Books Online.

For More Information For more information about partitioned views, see “Creating Partitioned Views” in SQL Server Books Online.


Advantages of Views


Advantages of using views

Views offer the following advantages:

Focus the data for a user. Views create a controlled environment that allows access to specific data while other data is concealed. Data that is unnecessary, sensitive, or inappropriate can be excluded from a view. Users can manipulate the display of data in a view, as is possible in a table. In addition, with the proper permissions and a few restrictions, users can modify the data that a view produces.

Mask database complexity. Views shield the complexity of the database design from the user. This provides developers with the ability to change the design without affecting user interaction with the database. In addition, users can see a friendlier version of the data because you can create views with names that are easier to understand than the cryptic names that are often used in databases.

Complex queries, including distributed queries to heterogeneous data, can also be masked through views. The user queries the view instead of writing the query or executing a script.

Simplify management of user permissions. Instead of granting permission for users to query specific columns in base tables, database owners can grant permission for users to query data through views only. This also protects changes in the design of the underlying base tables. Users can continue to query the view without interruption.

Improve performance. Views allow you to store results of complex queries. Other queries can use these summarized results. Views also allow you to partition data. You can place individual partitions on separate computers and seamlessly combine them for the user.

Organize data for export to other applications. You can create a view based on a complex query that joins two or more tables and then export the data to another application for further analysis.


Lesson 2: Creating and Managing Views



■ Describe the syntax for creating a view.

■ Explain how a view is created.

■ Describe the syntax for altering or dropping a view.

■ Describe how ownership chains affect views.

■ Describe the sources from which you can retrieve information about views.

■ Explain the purpose of view encryption.

■ Describe how data is updated through a view.

Introduction This lesson describes how to create, alter, and drop views. It also describes how to avoid broken ownership chains, how to hide view definitions, and how to obtain information on views within your database.


Syntax for Creating Views


Introduction You create views by executing the CREATE VIEW Transact-SQL statement or by using the graphical design interface provided in SQL Server Management Studio. As part of the view definition, you specify the contents of the view by using a SELECT statement.

Tip You should develop a consistent naming convention to distinguish views from tables. For example, you could add the letter v or the word view as a suffix to the name of each view that you create. This approach allows tables and views to be easily distinguished.

Creating views To create a view, use the View Designer in SQL Server Management Studio or the CREATE VIEW Transact-SQL statement. To open the View Designer in Object Explorer, expand the database that you want to work with, right-click the Views node, and then click New View. You can then design your view by using a graphical interface in which you can select the tables and columns to include in the view, define column relationships, restrict which rows are returned, and configure options such as column aliases and sort orders that are used to generate the view.

The CREATE VIEW statement has the following syntax.

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

[ WITH [ ENCRYPTION ] [, SCHEMABINDING ] [, VIEW_METADATA ] ]

AS select_statement [ ; ]

[ WITH CHECK OPTION ]

The following code demonstrates the creation of a view named HumanResources.vEmployee, which consists of a set of columns from a number of tables in the AdventureWorks database.

CREATE VIEW [HumanResources].[vEmployee]

AS

SELECT

e.[EmployeeID],c.[Title],c.[FirstName],c.[MiddleName],c.[LastName]

,c.[Suffix],e.[Title] AS [JobTitle],c.[Phone],c.[EmailAddress]


,c.[EmailPromotion],a.[AddressLine1],a.[AddressLine2],a.[City]

,sp.[Name] AS [StateProvinceName],a.[PostalCode]

,cr.[Name] AS [CountryRegionName],c.[AdditionalContactInfo]

FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Contact] c

ON c.[ContactID] = e.[ContactID]

INNER JOIN [HumanResources].[EmployeeAddress] ea

ON e.[EmployeeID] = ea.[EmployeeID]

INNER JOIN [Person].[Address] a

ON ea.[AddressID] = a.[AddressID]

INNER JOIN [Person].[StateProvince] sp

ON sp.[StateProvinceID] = a.[StateProvinceID]

INNER JOIN [Person].[CountryRegion] cr

ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

For More Information For more information about creating views, see “Query and View Designer Tools (Visual Database Tools)” and “CREATE VIEW (Transact-SQL)” in SQL Server Books Online.

Requirements for creating views

Remember the following facts when you are creating views:

■ You must be a member of the sysadmin role, the db_owner role, or the db_ddladmin role, or you must have been granted the CREATE VIEW permission in the database and the ALTER SCHEMA permission on the schema in which the view is to be created. You must also have SELECT permission on all tables or views that are referenced within the view.

■ You can create views only in the current database.

■ Your view name must follow the rules for identifiers and must be different from any other view or table name in the database.

■ You can build views on other views. Nesting cannot exceed 32 levels but might also be limited by the complexity of the views and the available memory.

■ Your view can contain a maximum of 1,024 columns.

■ You must specify column names if:

● Any of the columns of the view are derived from an arithmetical expression, built-in function, or constant.

● Any columns in the tables that will be joined share the same name.

■ You cannot create temporary views, and you cannot create views on temporary tables.

■ You cannot associate Rule or Default objects with a view.

■ You cannot associate AFTER triggers with views, only INSTEAD OF triggers.

■ You cannot include the COMPUTE or COMPUTE BY clause or the INTO keyword in the query that defines your view.

■ You cannot include the ORDER BY clause in the query that defines your view unless there is also a TOP clause in the select list of the SELECT statement.

■ You cannot include the OPTION clause specifying a query hint in the query that defines your view.

■ You cannot include the TABLESAMPLE clause in the query that defines your view.

For More Information For more information about the requirements for creating views, see “Designing and Implementing Views” in SQL Server Books Online.


Demonstration: Creating a View


Introduction In this demonstration, you will see how to create a view that displays data from multiple tables by using the graphical designer in SQL Server Management Studio. You will then see how to query that view, and finally how to generate the Transact-SQL script for the view.

Preparation Ensure that virtual machine 2779A-MIA-SQL-06 is running and that you are logged on as Student.

If a virtual machine has not been started, perform the following steps:

1. Close any other running virtual machines.

2. Start the virtual machine.

3. In the Log On to Windows dialog box, complete the logon procedure by using the user name Student and the password Pa$$w0rd.

Creating a view by using SQL Server Management Studio

Perform the following steps to create a view by using SQL Server Management Studio:

1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. In the Connect to Server dialog box, specify the values in the following table, and then click Connect.

3. If Object Explorer is not visible, click Object Explorer on the View menu.

4. In Object Explorer, expand Databases, AdventureWorks, and Views.

5. Right-click Views, and then click New View.

Property Value

Server type Database Engine

Server name MIAMI

Authentication Windows Authentication


6. Select the Contact (Person) and Employee (HumanResources) tables in the Add Table dialog box (hold down the CTRL key to select both tables), click Add, and then click Close.

7. Select EmployeeID and Title in the Employee (HumanResources) table.

8. Select Title, FirstName, MiddleName, LastName, EmailAddress, and Phone in the Contact (Person) table.

9. Change the Alias of the Person.Contact.Title column from Expr1 to Salutation.

10. If the Properties window is not visible, click Properties Window on the View menu.

11. Set the view’s Schema property to HumanResources.

12. On the File menu, click Save View – dbo.View_1.

13. Enter the name vEmployeeContactView for the new view, and then click OK.

14. Keep SQL Server Management Studio open. You will use it in the next procedure.

Querying a view To view the contents of the vEmployeeContactView view by using SQL Server Management Studio:

1. In SQL Server Management Studio, click the New Query button on the toolbar.


3. In the new, blank query window, type the following Transact-SQL code.

USE AdventureWorks

GO

SELECT * FROM

HumanResources.vEmployeeContactView

4. Click the Execute button on the toolbar.

5. When the command has completed, view the returned results.

6. Keep SQL Server Management Studio open. You will use it in the next procedure.

Generating a script for a view

To view the Transact-SQL used to create the vEmployeeContactView view:

1. In Object Explorer, right-click the Views node under the AdventureWorks database, and then click Refresh.

2. Right-click the HumanResources.vEmployeeContactView view, click Script View as, click CREATE To, and then click New Query Editor Window.

3. Review the CREATE VIEW Transact-SQL statement.

4. Close SQL Server Management Studio. If prompted to save any files, click No.

Property Value


Server name MIAMI



Syntax for Altering and Dropping Views


Introduction If you need to change a view, you can modify it either by using SQL Server Management Studio or by executing the ALTER VIEW Transact-SQL statement.

If you no longer need a view, you can remove its definition from the database either by using SQL Server Management Studio or by executing the DROP VIEW Transact-SQL statement.

Altering views You can change your view’s definition by opening the View Designer tool in Object Explorer or by using the ALTER VIEW Transact-SQL statement. You can modify the tables and columns included in the view, change column relationships, restrict which rows the view returns, and modify options like column aliases and sort orders that are used to generate the view.

The ALTER VIEW statement changes the definition of a view, including indexed views, without affecting dependent stored procedures or triggers. This allows you to retain permissions for the view. This statement is subject to the same restrictions as the CREATE VIEW statement. The ALTER VIEW statement has the following syntax.

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ]

[ WITH <view_attribute> [ ,...n ] ]

AS select_statement [ ; ]

[ WITH CHECK OPTION ]

<view_attribute> ::=

{

[ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ]

}


The following code modifies the HumanResources.vEmployee view from the AdventureWorks database and removes all postal address information from the view.

ALTER VIEW [HumanResources].[vEmployee]

AS

SELECT

e.[EmployeeID]

,c.[Title]

,c.[FirstName]

,c.[MiddleName]

,c.[LastName]

,c.[Suffix]

,e.[Title] AS [JobTitle]

,c.[Phone]

,c.[EmailAddress]




For More Information For more information about altering views, see “Query and View Designer Tools (Visual Database Tools)” and “ALTER VIEW (Transact-SQL)” in SQL Server Books Online.

Dropping views Dropping a view removes its definition and all permissions assigned to it. Furthermore, if users query any views that reference the dropped view, they receive an error message. However, dropping a table that is referenced by a view does not drop the view automatically. You must drop the view explicitly.

You can drop a view by deleting it in Object Explorer or by using the DROP VIEW statement. The DROP VIEW statement has the following syntax.

DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]

The following code demonstrates the statement used to drop the HumanResources.vEmployee view from the AdventureWorks database.

DROP VIEW [HumanResources].[vEmployee]

For More Information For more information about the syntax of the DROP VIEW statement, see “DROP VIEW (Transact-SQL)” in SQL Server Books Online.


How Ownership Chains Affect Views


Introduction Views depend on underlying views or tables. As SQL Server resolves the contents of a view, it traverses the hierarchy of table and view dependencies to retrieve the appropriate content. When multiple database objects access each other sequentially in this way, the sequence is known as a chain. SQL Server evaluates permissions on objects in a chain differently than it would if the objects were accessed separately.

Note You can configure SQL Server to allow ownership chaining between specific databases or across all databases inside a single instance of SQL Server. Cross-database ownership chaining is disabled by default, and you should not enable it unless it is specifically required.

How SQL Server checks permissions in an ownership chain

When a user accesses a database object (such as a view) and that object accesses another object (such as a table), SQL Server does not check the user’s permissions on the second object if the owner of both objects is the same. This means that when a user has permission to access a view, SQL Server does not evaluate the user’s permissions on each underlying table or view as long as they are owned by the owner of the original view. Only when accessing an object with a different owner does SQL Server evaluate the permissions of the user against that object.

Ownership chaining allows you to manage access to multiple objects, such as multiple tables, by setting permissions on one object, such as a view. Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks.

To avoid broken ownership chains, ensure that all views and the underlying tables and functions have the same owner.

For More Information For more information about ownership chains, see “Ownership Chains” in SQL Server Books Online.


Sources of Information About Views


Introduction You might require information about existing views before you create, modify, or remove a view. In SQL Server 2005, you can use the following sources to obtain information about views:

■ SQL Server Management Studio

■ The sys.views catalog view

■ The sp_helptext system stored procedure

■ The sys.sql_dependencies catalog view

Obtaining information about views by using SQL Server Management Studio

To obtain information about a view by using SQL Server Management Studio, open Object Explorer and expand the database that you want to work with. Expand the Views node to see a list of available views. Expand a particular view to access the columns, triggers, indexes, and statistics defined for the view. Alternatively, right-click the view to display a shortcut menu that allows you to generate scripts used to create, alter, and drop the view, as well as to open the view’s Property dialog box.

For More Information For more information about working with views in SQL Server Management Studio, see “Working with Views (Visual Database Tools)” in SQL Server Books Online.

Obtaining a list of views by using Transact-SQL

You can query the sys.views catalog views to obtain general information about available views. The result set will contain a row for each available view. The following example returns a list of all available views in the AdventureWorks database.

USE AdventureWorks

GO

SELECT * FROM sys.views

For More Information For more information about the sys.views catalog view, see “sys.views (Transact-SQL)” in SQL Server Books Online.


Obtaining the definition of a view by using Transact-SQL

To view the definition of an unencrypted view, use the sp_helptext system stored procedure, passing the view name as an argument. The sp_helptext stored procedure will return an error if the specified view is encrypted. The following example returns the definition of the HumanResources.vEmployee view from the AdventureWorks database.

USE AdventureWorks

GO

EXEC sp_helptext 'HumanResources.vEmployee'

For More Information For more information about the sp_helptext stored procedure, see “sp_helptext (Transact-SQL)” in SQL Server Books Online.

Determining view dependencies by using Transact-SQL

Before you drop any table or view, you should check to determine whether any view depends on it by using the sys.sql_dependencies catalog view. The sys.sql_dependencies view contains a row for each dependency on a referenced table or view. The following example demonstrates how to get a list of all database objects that have direct dependencies on the HumanResources.Employee table from the AdventureWorks database.

USE AdventureWorks

GO

SELECT DISTINCT OBJECT_NAME(object_id) AS Name

FROM sys.sql_dependencies

WHERE referenced_major_id =

OBJECT_ID(N'AdventureWorks.HumanResources.Employee')


View Encryption


Purpose of view encryption

The text used in the CREATE VIEW or ALTER VIEW statement to define a view is stored in the sys.syscomments system table. To protect the logic that defines a view, specify the WITH ENCRYPTION option. This encrypts the text stored in sys.syscomments so that people cannot read it.

Tip You should always store a copy of the CREATE VIEW or ALTER VIEW statement in a secure location before you create an encrypted view; otherwise, you will not be able to access the view’s definition if you need it in the future.

Example of view encryption

The following code alters the HumanResources.vEmployee view in the AdventureWorks database and enables encryption.

ALTER 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]




Tip If you create an encrypted view, when you modify it, you must specify the WITH ENCRYPTION option; otherwise, the encryption will be disabled.


Considerations for Modifying Data in a View


Introduction Views do not maintain a separate copy of data. Instead, they show the result set of a query on one or more base tables. Therefore, whenever you modify data in a view, you are actually modifying the base table. With some restrictions, you can insert, update, or delete table data freely through a view.

Restrictions on modifying data in a view

The following restrictions apply when you try to modify data in a view:

■ Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

■ Any columns being modified must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as from an aggregate function or a computation using other columns.

■ Any columns being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

Using CHECK OPTION to limit updates to view scope

All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.

For More Information For more information about modifying data in views, see “Modifying Data Through a View” in SQL Server Books Online.


Practice: Creating a View


Goals The goal of this practice is to enable you to create a view by using Transact-SQL.






To create a view by using Transact-SQL

� You must perform the following steps to create a view by using Transact-SQL:

1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and click SQL Server Management Studio.


3. If Object Explorer is not visible, click Object Explorer on the View menu.

4. In Object Explorer, expand Databases, AdventureWorks, and Views.

5. On the File menu, point to Open, and click File.

6. Browse to the D:\Practices folder and open CreateView.sql. When prompted, connect to MIAMI by using Microsoft Windows® authentication.

Property Value


Server name MIAMI



7. Review the following code in the query window.

USE AdventureWorks

GO

CREATE VIEW [HumanResources].[vEmployeeContact]

WITH ENCRYPTION AS SELECT

e.EmployeeID,

e.Title,

c.Title AS Salutation,

c.FirstName,

c.MiddleName,

c.LastName,

c.EmailAddress,

c.Phone

FROM Person.Contact c INNER JOIN HumanResources.Employee e

ON c.ContactID = e.ContactID

8. Click the Execute button on the toolbar.

9. When the command has completed successfully, right-click the Views folder in Object Explorer, and then click Refresh to verify that the HumanResources.vEmployeeContact view has been created.

10. Click the New Query button on the toolbar.

11. In the new, blank query window, type the following Transact-SQL code.

USE [AdventureWorks]

GO

SELECT * FROM

[HumanResources].[vEmployeeContact]

12. On the toolbar, click the Execute button.

13. Verify that data is returned from the view.

14. Close SQL Server Management Studio. Click No if prompted to save files.


Lesson 3: Optimizing Performance by Using Views



■ Describe the performance considerations for views.

■ Explain the purpose of an indexed view and when to use it.

■ Describe partitioned views and their performance benefits.

Introduction This lesson describes performance considerations for using views and how views allow you to optimize performance by storing results of complex queries and partitioning data.


Performance Considerations for Views


Performance considerations for views

When you use a standard view, there is an inherent performance overhead because SQL Server must execute SELECT statements against one or more tables to retrieve data for the view each time it is accessed. This overhead is increased if views are nested. You must be careful when nesting views that you don’t inadvertently introduce a long chain of nested views that will have a significant impact on query performance.

You can easily determine whether views are nested by looking at the view’s definition to see whether it relies on tables or views. However, when a view is encrypted, it is not possible to look at the definition. To evaluate the performance of a view and to identify the actions performed by a nested view that is encrypted, you can use SQL Server Profiler.

For More Information For more information about improving the performance of views, see “Using SQL Server Profiler” in SQL Server Books Online.

Improving performance of views

Two approaches are commonly used to improve the performance of views:

■ Indexed views

■ Partitioned views


What Is an Indexed View?


Definition An indexed view is a view that has a unique clustered index created on it. An indexed view stores the result set of a view in the leaf-level pages of the index. SQL Server can quickly reference the index to obtain data from the view.

Performance benefits of indexed views

As modifications are made to the data in the base tables, the data modifications are reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification. Because of the improved retrieval time, you can use indexed views to improve query performance.

For More Information For more information about implementing an indexed view, see “Designing Indexed Views” in SQL Server Books Online.

Another benefit of creating an index on a view is that the query optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.

For More Information For more information about how the query optimizer uses views, see “Resolving Indexes on Views” in SQL Server Books Online.

Considerations for using indexed views

The disadvantage of creating indexed views is the overhead of maintaining the index. You should consider using an indexed view when:

■ Query performance gains outweigh the maintenance overhead.

■ Underlying data is updated infrequently.

■ Queries perform a significant number of joins and aggregations that either process many rows or are performed frequently by many users.


Requirements for an indexed view

There are many requirements and limitations associated with creating indexed views. Here are the major requirements:

■ The first index that you create on a view must be a unique clustered index.

■ The view must be defined by using the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

■ The view can reference base tables, but it cannot reference other views.

■ The base tables referenced by the view must be in the same database as the view and have the same owner as the view.

■ The tables and user-defined functions referenced by the view must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

■ The functions referenced by expressions in the view must be deterministic.

For More Information For more information about creating indexed views, see “Creating Indexed Views” in SQL Server Books Online.

Example of an indexed view

You create indexes on views by using the CREATE INDEX statement. The following example creates a unique clustered index named IX_vStateProvinceCountryRegion on the Person.vStateProvinceCountryRegion view of the AdventureWorks database.

USE [AdventureWorks]

GO

CREATE UNIQUE CLUSTERED INDEX [IX_vStateProvinceCountryRegion] ON

[Person].[vStateProvinceCountryRegion]

(

[StateProvinceID] ASC, [CountryRegionCode] ASC

)

For More Information For more information about the CREATE INDEX statement, see “CREATE INDEX (Transact-SQL)” in SQL Server Books Online.


What Is a Partitioned View?


Definition A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if it is from one table. The partitioned view uses UNION ALL clauses to combine the results of SELECT statements on all the member tables into a single result set.

Types of partitioned views

SQL Server 2005 distinguishes between local and distributed partitioned views. In a local partitioned view, all participating tables and the view reside on the same instance of SQL Server. Local partitioned views are included in SQL Server 2005 for backward compatibility only. The preferred method for partitioning data locally is by using partitioned tables.

For More Information For more information about partitioned tables, see “Partitioned Tables and Indexes” in SQL Server Books Online.

In a distributed partitioned view, at least one of the participating tables resides on a different (remote) server. In addition, SQL Server 2005 differentiates between partitioned views that are updatable and views that are read-only copies of the underlying tables.

For More Information For more information about partitioned views, see “Using Partitioned Views” in SQL Server Books Online.

Performance benefits of partitioned views

If the tables in a partitioned view are on different servers, or on a computer with multiple processors, each table involved in the query can be scanned in parallel, thereby improving query performance. In addition, maintenance tasks, such as rebuilding indexes or backing up a table, can execute faster.

Note You cannot create an index on a partitioned view. The view definition required to build the indexed view allows only two-part names; a partitioned view requires the use of three-part or four-part names, such as servername.databasename.schema.objectname.


Federated servers and partitioning

Distributed partitioned views are used to implement a federation of database servers. A federation is a group of servers that are administered independently but that cooperate to share the processing load of a system. Forming a federation of database servers by partitioning data is the mechanism that enables you to scale out a set of servers to support the processing requirements of large, multitiered Web sites.

For More Information For more information about federated database servers, see “Federated Database Servers” in SQL Server Books Online.


Lab: Implementing Views


Scenario After earlier consultation with the Human Resources department, the senior database developer has identified a number of views that will improve database use and simplify future development. The senior database developer has asked you to perform the following tasks:

■ Create a new view named HumanResources.vEmployeeDetails that uses the following SELECT logic. Remember to use the SCHEMABINDING option when you create the view because you will need to create an index on the view later.

SELECT

e.[EmployeeID]

,c.[Title]

,c.[FirstName]

,c.[MiddleName]

,c.[LastName]

,c.[Suffix]

,e.[Title] AS [JobTitle]

,c.[Phone]

,c.[EmailAddress]

,c.[EmailPromotion]

,a.[AddressLine1]

,a.[AddressLine2]

,a.[City]

,sp.[Name] AS [StateProvinceName]

,a.[PostalCode]

,cr.[Name] AS [CountryRegionName]

,c.[AdditionalContactInfo]




INNER JOIN [HumanResources].[EmployeeAddress] ea

ON e.[EmployeeID] = ea.[EmployeeID]

INNER JOIN [Person].[Address] a

ON ea.[AddressID] = a.[AddressID]

INNER JOIN [Person].[StateProvince] sp

ON sp.[StateProvinceID] = a.[StateProvinceID]

INNER JOIN [Person].[CountryRegion] cr

ON cr.[CountryRegionCode] = sp.[CountryRegionCode]


■ After testing the HumanResources.vEmployeeDetails view, turn it into an indexed view by creating a unique clustered index named IX_vEmployeeDetails on the EmployeeID column.

■ Create a distributed partitioned view named Person.vContact on the AW_Contacts database. The view should union the sets of data contained in the following source servers and tables:

● [MIAMI].AW_Contacts.Person.Contact

● [MIAMI\SQLINSTANCE2].AW_Contacts.Person.Contact

● [MIAMI\SQLINSTANCE3].AW_Contacts.Person.Contact

Additional information When performing database development tasks, it can be helpful to use SQL Server Management Studio to create a SQL Server Scripts project, and use it to document the Transact-SQL code necessary to re-create the solution if necessary.

Use the following procedure to create a SQL Server Scripts project:

1. Open SQL Server Management Studio, connect to the server you want to manage.

2. On the File menu, point to New, and then click Project.

3. Select the SQL Server Scripts template and enter a suitable name and location for the project. Note that you can create a solution that contains multiple projects, but in many cases a single project per solution is appropriate.

To add a query file to a project:

1. Click New Query on the Project menu, or right-click the Queries folder in Solution Explorer and click New Query. If Solution Explorer is not visible, you can display it by clicking Solution Explorer on the View menu.

2. When prompted, connect to the server on which you want to execute the query. This will add a connection object to the project.

3. Change the name of the query file from the default name (SQLQuery1.sql) by right-clicking it in Solution Explorer and clicking Rename.

Although you can perform all database development tasks by executing Transact-SQL statements, it is often easier to use the graphical user interface in SQL Server Management Studio. However, you should generate the corresponding Transact-SQL scripts and save them in the project for future reference.

Often, you can generate the Transact-SQL script for an action before clicking OK in the Properties dialog box used to perform the action. Many Properties dialog boxes include a Script drop-down list with which you can script the action to a new query window, a file, the Clipboard, or a SQL Server Agent job. A common technique is to add a blank query file to a project, and then script each action to the Clipboard as it is performed and paste the generated script into the query file.

You can also generate scripts for many existing objects, such as databases and tables. To generate a script, right-click the object in Object Explorer and script the CREATE action. If Object Explorer is not visible, you can display it by clicking Object Explorer on the View menu.


Exercise 1: Creating ViewsCreating a view

Task Supporting information

Create a SQL Server Scripts project.

1. Create a new SQL Server Scripts project named AW_Views in the appropriate folder.

2. Add a new query to the project, connecting to MIAMI by using Windows authentication when prompted.

3. Change the query file name to CreateEmployeeView.sql.

Create the HumanResources.vEmployeeDetails view.

1. In the query window, type the appropriate Transact-SQL statement to create the HumanResources.vEmployeeDetails view in the AdventureWorks database.

2. Execute the query, and then save the query file.

3. Use Object Explorer to verify that the view has been created.

Test the HumanResources.vEmployeeDetails view.

1. Create a new query containing the following code:

Use AdventureWorks

SELECT * FROM

[HumanResources].[vEmployeeDetails]

2. Execute the query and verify that data is returned from the view.


Exercise 2: Creating Indexed ViewsCreating an indexed view


Create a new query file. 1. Add a new query to the project, connecting to MIAMI by using Windows authentication when prompted.

2. Change the query file name to CreateViewIndex.sql.

Create the IX_vEmployeeDetails index.

1. In the query window, type the appropriate Transact-SQL statement to create the unique clustered index named IX_vEmployeeDetails on the HumanResources.vEmployeeDetails view in the AdventureWorks database.


3. Use Object Explorer to verify that the index has been created.


Exercise 3: Creating Partitioned ViewsCreating a distributed partitioned view

Results checklist Use the following checklist of results to verify whether you have successfully performed this lab:

■ Created a view named HumanResources.vEmployeeDetails

■ Created an index on the HumanResources.vEmployeeDetails view

■ Created a distributed partitioned view named Person.vContact


Prepare the SQL Server instances.

1. In Windows Explorer, view the contents of the D:\Labfiles\Starter folder.

2. Double-click LabSetup.cmd to run the script that configures the servers for the partitioned view.

Create a new query file. 1. Add a new query to the project, connecting to MIAMI by using Windows authentication when prompted.

2. Change the query file name to CreatePartitionedView.sql.

Create the Person.vContact distributed partitioned view.

1. In the query window, type the appropriate Transact-SQL statement to create the distributed partitioned view Person.vContact on the AW_Contacts database.


3. Use Object Explorer to verify that the view has been created.

4. Create a new query. When prompted, connect to MIAMI.

5. In the query window, type the appropriate Transact-SQL to select all rows from the Person.vContact view on the AW_Contacts database.

6. Execute the query and verify that results are returned from the partitioned view.

9767406 - implementing views

Education