Creating and Implementing Views

Introduction

The Creating and Implementing Views module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with Query and View Designer tool
• Updating data in views
• Working with partitioned views
• Working with indexed views

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Five exam objectives are covered in this lab.

• Demonstrate how to design a view using Query and View Designer tool
• Demonstrate how to update a view using SQL Server Management Studio
• Demonstrate how to update a view using Transact-SQL query
• Demonstrate how to create a partitioned view for a table with four member tables
• Demonstrate how to create an index on a view

Exercise 1 - Working with Query and View Designer Tool

A view can be compared to a table that consists of data, arranged in columns and rows. The data in a view are defined by a simple or complex query. Views or ‘virtual’ tables are created to restrict the access of tables that store sensitive or critical data. Unauthorized users can view only restricted database data with the help of views. Views also help in obtaining aggregate data, such as, the total number of rows or columns, average value, and percentage of amount. Views can be created only in the current database and contains data from a single table, multiple tables, or another view. In addition to user-defined views, there are other types of views such as indexed views, partitioned views, and system views.

The SQL Server Management Studio has an in-built Query and View Designer tool to design a view. There are four panes in the tool such as the Diagram pane, Grid pane, SQL pane, and the Results pane.

In this exercise, you will learn to design a view using the Query and View Designer tool.

Exercise 2 - Updating Data in Views

You can update data in a view by either using SQL Server Management Studio or Transact-SQL. When you update the data in a view, the underlying base table data is updated. You can update the base table data through a view if the view references only a single table.

In this exercise, you will learn how to use SQL Server Management Studio and Transact-SQL to update a view.

Exercise 3 - Working with Partitioned Views

Partitioned views enable you to divide a large database table into smaller member tables. These member tables can be in the same or different databases or across multiple servers. The member tables are also known as base tables. The member tables are combined together with the help of UNION ALL in partitioned views. Thus, the partitioned views appear as a single table. It is recommended to create partitioned views to balance the input, processing, and output load of huge databases across multiple servers.

You need to follow certain rules while creating the partitioned views. Some of them are as follows:

• A partitioning column that is a primary key part of the member table must exist in the view.
• All the columns of the member tables must be included when you create the partitioned view.
• The member tables must have a CHECK constraint on the partitioning column.

There are two types of partitioned views such as local and distributed. In a local partitioned view, the member tables are from the same instance of SQL Server. In a distributed partitioned view, the member tables are from different SQL Servers.

In this exercise, you will learn to create a partitioned view for a table with four member tables.

Exercise 4 - Working with Indexed Views

You can create indexed views in SQL Server using Transact-SQL statements. The query optimizer uses the indexed views to determine the query plan of the database data and to accelerate the process of query execution.

In this exercise, you will learn to create a new view using the WITH SCHEMABINDING option. Then, you will create a unique clustered index on the newly created view.