Transcript Session3_Module5

Session 3
Module 5: Implementing Views
Module 6: Managing Views
Views / Session 3/ 1 of 40
Module 3 - Review







Indexes are of two types, clustered and nonclustered.
In a clustered index, data is physically sorted. Hence, a
table can have only one clustered index.
In a nonclustered index, data is not physically sorted,
only pointers are created to point to the physical location
of the data. Hence, a table can have multiple
nonclustered indexes.
Indexes can be created using the CREATE INDEX
command.
An index that uses more than one column to index data
is called a composite index.
The FILLFACTOR and PAD_INDEX options reserve
space on index pages for future index expansion.
The sp_helpindex system store procedure is used to
view index information.
Database Objects/ Session 3/ 2 of 40
Module 4 - Review





New index features: online index operations, parallel
index operations and locking options.
The ALTER INDEX statement is used to reorganize,
disable and rebuild an index.
SQL Server 2005 allows non-key columns to be
included in nonclustered indexes and allows you to
create XML indexes on the XML columns in the table.
The various methods to optimize indexes include
reorganizing, rebuilding, disabling and dropping
indexes.
SQL Server 2005 creates index statistics that provide
information about the distribution of values in a
column or a group of columns. This information is
used by the query optimizer to speed up the query
process.
Database Objects/ Session 3/ 3 of 40
Implementing Views
Objectives
Database Objects/ Session 3/ 4 of 40
Introduction to Views

A view is a virtual table that can be created to
allow users access to specific columns of a
table.

Views can include columns from multiple tables.
A view retrieves data from multiple tables and
presents it in such a way that the data appears
to have been retrieved from a single table.

The tables from which the view is created are
referred to as base tables. These tables can be
from different databases.
Database Objects/ Session 3/ 5 of 40
Types of Views – 1

Views can be classified into three different
types based on the parameters and
options specified at the time of creating
the view.

Standard View

Indexed View

Partitioned View
Database Objects/ Session 3/ 6 of 40
Types of Views – 2

Standard View: A view created using columns from one or more
tables is referred to as a standard view. Most of the advantages of
views can be satisfied using standard views. These include
providing specific access to data for viewing and manipulation.

Indexed View: A view defined with a unique clustered index is
referred to as an indexed view. Indexed views are useful for queries
that aggregate many rows. Indexed views are not suitable for tables
that are frequently updated.

Partitioned View: A view created using horizontally partitioned data
from one or more tables is referred to as a partitioned view. If the
base tables reside in the same server, the view is referred to as a
local partitioned view. If the base tables reside in different servers,
the view is referred to as a distributed partitioned view.
Database Objects/ Session 3/ 7 of 40
Advantages of Views – 1

Views provide a personalized image of selected
parts of a table. There are multiple advantages
of views.

Security through Personalized Access: Views provide
a data access system through which users can be
allowed to access specific data columns.

Customized Display of Data: Using vies, data derived
from the same source can be viewed differently by
different users depending on individual viewing
requirements.
Database Objects/ Session 3/ 8 of 40
Advantages of Views – 2

Merger of Data from Multiple Tables or Views: Views
can merge columns from multiple tables and other
views and display them together as if coming from a
single table.

Operations on Records: Views can be used to update
tables provided all the important columns of the table
are contained in the view.

Integrity Constraint Checks: If data is added or
modified through a view, SQL Server automatically
checks the data to ensure that it meets the data
integrity constraints for the respective columns.
Database Objects/ Session 3/ 9 of 40
System Views

The properties of an object such as a table or a view are
stored in special system tables. These properties are
referred to as metadata. All SQL objects produce
metadata.

This metadata can be viewed using system views, which
are predefined views of SQL Server 2005.

There are 230 different system views and these are
automatically inserted into the user created database.
These views are grouped into several different schemas
(sub-databases).
Database Objects/ Session 3/ 10 of 40
Creating Views – 1

A user can create a view using columns
from tables or other views only if the user
has permission to access these objects.

A view is created using the CREATE
VIEW statement and it can be created only
in the current database.

A view can have a maximum of 1024
columns.
Database Objects/ Session 3/ 11 of 40
Creating Views – 2

Syntax:
CREATE VIEW <view_name>
AS <select_statement>
where


select_statement: specifies the SELECT statement that defines the view.
Example:
CREATE VIEW Salary_Details AS
SELECT EmpID, FirstName, LastName, Salary
FROM Employee_Details
To display the details of the Salary_Details view
SELECT * FROM Salary_Details
Database Objects/ Session 3/ 12 of 40
Permission

To execute the CREATE VIEW statement, the
user must be a member of the system
administrators (sysadmin) role, database owner
(db_owner), the data definition language
administrator (db_ddladmin) or the user must
have the CREATE VIEW permission.

Also, the user should have the SELECT
permission on all tables or views that are
references within the query.
Database Objects/ Session 3/ 13 of 40
Creating Views
using “JOIN” Keyword

Syntax:
CREATE VIEW <view_name> AS
SELECT * FROM table_name1
JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Example:
CREATE VIEW EmpDetails AS
SELECT EmpID, FirstName, LastName, DeptName
FROM Department JOIN Employee
ON Department.DeptID = Employee. DeptID
Database Objects/ Session 3/ 14 of 40
Restrictions

The CREATE VIEW statement cannot include the
COMPUTE or COMPUTE BY clauses.

The CREATE VIEW statement can include the ORDER
BY clause only if the TOP keyword is used.

Views cannot reference more than 1024 columns.

The CREATE VIEW statement cannot include the INTO
keyword.

A view cannot reference a temporary table.

The CREATE VIEW statement cannot be combined with
other Transact-SQL statements in a single batch.
Database Objects/ Session 3/ 15 of 40
Altering Views

Syntax:
ALTER VIEW <view_name> AS <select_statement>

Example:
ALTER VIEW Salary_Details AS
SELECT EmpID, FirstName, LastName, City, Salary
FROM Employee_Details WHERE City = ‘New York’
To display the details of the Salary_Details view
SELECT * FROM Salary_Details
Database Objects/ Session 3/ 16 of 40
Dropping Views

A view can be removed from the database if it is no
longer needed. This is done using the DROP VIEW
statement. When a view is dropped, the data in the base
tables remains unaffected.

The definition of the view and other information
associated with the view is deleted from the system
catalog. All permissions for the view are also deleted.

If a user queries any view that references the dropped
view, the user receives an error message.
Database Objects/ Session 3/ 17 of 40
Viewing Definition

There are certain system stored
procedures that help retrieve view
definitions: sp_helptext

Syntax: sp_helptext <view_name>

Example: sp_helptext ‘DeptEmpView’

Information about the definition of a view
can be obtained if such information is not
encrypted.
Database Objects/ Session 3/ 18 of 40
Locating
View Dependencies

Syntax: sp_depends <objname>
where,


objname: specifies the name of
database object for depencencies.
the
Example: sp_depends ‘Salary_Details’
Database Objects/ Session 3/ 19 of 40
Hiding View Definition

Syntax:
CREATE VIEW <view_name>
WITH ENCRYPTION AS <select_statement>

Example:
CREATE VIEW Salary_Details
WITH ENCRYPTION AS
SELECT EmpID, FirstName, LastName, Salary
FROM Employee_Details

If a user tries to see the view definition, an error message will be
displayed stating the definition is encrypted.
Database Objects/ Session 3/ 20 of 40
Module 5 – Summary (1)




A view is a virtual table generated using selected
columns from one or more tables.
There are three types of views namely,
Standard, Indexed and Partitioned.
Views are used to grant users limited access to
database tables depending on individual user
requirements.
System views are predefined created views for
extracting SQL Server metadata.
Database Objects/ Session 3/ 21 of 40
Module 5 – Summary (2)




The CREATE VIEW statement is used to create a view.
The view definition cannot include the COMPUTE,
COMPUTE BY, ORDER BY or OPTION clauses.
In SQL Server 2005, you can create views having
extracted information from xml data type columns of a
table. This extraction is done using the value() method.
After creating a view, you can also alter or delete it.
Database Objects/ Session 3/ 22 of 40
Module 6: Managing Views
Objectives
Database Objects/ Session 3/ 23 of 40
The “CHECK OPTION”
Option – 1

It is used to ensure that all the updates in
the view satisfy the conditions mentioned in
the view definition. If the conditions are not
satisfied, the database engine returns an
error.

Thus, the CHECK OPTION is used to
enforce domain integrity; it checks the
definition of the view to see that the
WHERE conditions in the SELECT
statement is not violated.
Database Objects/ Session 3/ 24 of 40
The “CHECK OPTION”
Option – 2

Syntax:
CREATE VIEW <view_name>
AS <select_statement> [WITH CHECK OPTION]
where:


WITH CHECK OPTION: specifies that the modified data in the view
continues to satisfy the view definition.
Example:
CREATE VIEW Emp_Salary_Details AS
SELECT EmpID, FirstName, LastName, City, Salary
FROM Employee_Details WHERE Salary < 35000
WITH CHECK OPTION
Database Objects/ Session 3/ 25 of 40
SCHEMABINDING” Option – 1

A view can be bound to the schema of the base table
using the SCHEMABINDING option. This option can be
used with CREATE VIEW or ALTER VIEW statements.

When SCHEMABINDING option is specified, the base
table or tables cannot be modified that would affect the
view definition.

The view definition must be first modified or deleted to
remove dependencies on the table that is to be
modified.

While using the SCHEMABINDING option in a view, you
must specify the schema name along with the object
name in the SELECT statement.
Database Objects/ Session 3/ 26 of 40
“SCHEMABINDING” Option – 2

Syntax:
CREATE VIEW <view_name> [WITH SCHEMABINDING]
AS <select_statement>
where:

WITH SCHEMABINDING: specifies that the view must be bound to
a schema.

Example:
CREATE VIEW Employee_View WITH SCHEMABINDING AS
SELECT EmpID, FirstName, LastName, City
FROM dbo.Employee_Details
Database Objects/ Session 3/ 27 of 40
Guidelines

Tables that are schema-bound to a view
cannot be dropped unless the view is dropped
or changed such that it no longer has schema
binding. If the view is not dropped or changed
and you attempt to drop the table, the
Database Engine returns an error message.

Also, when an ALTER TABLE statement
affects the view definition of a schema-bound
view, the ALTER TABLE statement fails.
Database Objects/ Session 3/ 28 of 40
Indexed Views – 1

An indexed view is a view that has a UNIQUE CLUSTERED index
created on it. Unlike regular views, which are generated only at
runtime, an indexed view stores the result set of a view in the
database.

After an index is created on a view, if any modifications are made
to the data in the base tables, they are automatically updated in
the indexed view.

Indexed views improve performance due to sorting of data. It
works best for queries that contain a large number of rows.

To create an index on a view, it is necessary to first bind the view
to a schema. This is done using the SCHEMABINDING option.
Database Objects/ Session 3/ 29 of 40
Indexed Views – 2

To create an index on a view, it is necessary to first bind the view
to a schema. This is done using the SCHEMABINDING option.

Syntax:
CREATE UNIQUE CLUSTERED INDEX <index_name>
ON <view_name> (column_name,…)

Example:
CREATE UNIQUE CLUSTERED INDEX IX_EmpID
ON Employee_View (EmpID)

Indexed views should not be used for tables that are frequently
updated as this puts additional strain on resources for maintaining
the view.
Database Objects/ Session 3/ 30 of 40
Guidelines

The following guidelines should be considered before creating an indexed
view:

The speed of retrieving the data must outweigh the maintenance cost.

The underlying data should have minimal updating requirements.

Indexes should be used in queries that include a significant amount of
joins and aggregations processing many rows and performed
frequently.

The first index created on the view must be a unique clustered index.

Create the view with the SCHEMABINDING option.

An indexed view can reference base tables, but not other views.
Database Objects/ Session 3/ 31 of 40
Modifying and Renaming a View

Syntax:
sp_rename
‘<object_name>’,
‘<object_type>’
‘<new_name>’,
where:

object_type: specifies the type of object being
renamed. For example, COLUMN, DATABASE,
INDEX, OBJECT and USERDATATYPE.
Example:
sp_rename ‘Employee_View’, ‘DeptEmpView_NEW’

Database Objects/ Session 3/ 32 of 40
Modifying Data through Views

Views can be used to modify data in database tables. When
modifying data through a view, the following points should be
considered:

Modifications can be carried out in columns from a single base
table.

Data cannot be modified if the column is created using
aggregate functions.

When the WITH CHECK OPTION clause is used, values cannot
be modified that causes them to violate the view definition.

Records can be modified only if the view contains all the
columns of the base table that have constraints defined on
them.
Database Objects/ Session 3/ 33 of 40
“UPDATE” with Views

Data in rows of a view can be updated using the UPDATE
statement. These updates can be carried out on a single row,
group of rows, or on the entire table. When a view is updated,
corresponding updates are made in the base table.

Syntax:
UPDATE <view_name>
SET<column_1>=<value_1>, <column_2>=<value_2>
WHERE <search_condition>

Example:
UPDATE CustDetails SET City=‘San Francisco’
WHERE CustID=‘C0004’
Database Objects/ Session 3/ 34 of 40
Guidelines

You can insert or update a multi-table view if:

The view has no WITH CHECK OPTION clause.

All columns being inserted or updated belong to the same base table.

The value of a column with an IDENTITY property cannot be
updated.

Records cannot be updated if the base table contains a
TIMESTAMP column.

While updating a row, if a constraint or rule gets violated, the
statement is terminated, an error is returned and no records are
updated.

When there is a self join with the same view or base table, the
UPDATE statement does not work.
Database Objects/ Session 3/ 35 of 40
“INSERT” with Views

SQL Server 2005 enables you to insert rows into a
view. New rows can be inserted using the INSERT
statement. When rows are inserted into the view, the
base table get accordingly updated.

Syntax:
INSERT INTO <view_name> (<column_1>, <column_2>)
VALUES (<value_1>, <value_2>)

Example:
INSERT
INTO
(CustID,AccNo,AccName,City)
CustDetails
VALUES (‘C0005’, 5, ‘Joes’, ‘Sacramento’)
Database Objects/ Session 3/ 36 of 40
Guidelines

The INSERT statement must specify values
for all columns in a view in the underlying
table that do not allow null values and have
no DEFAULT definitions.

When there is a self-join with the same view
or base table, the INSERT statement does
not work.
Database Objects/ Session 3/ 37 of 40
“DELETE” with Views

SQL Server 2005 enables you to delete rows from a
view. Rows can be deleted from the view using the
DELETE statement. When rows are deleted from a
view, corresponding rows get deleted from the base
table.

Syntax:
DELETE FROM <view_name>
WHERE (<search_condition>)

Example:
DELETE FROM CustDetails WHERE CustID=‘C0004’
Database Objects/ Session 3/ 38 of 40
Module 6 - Summary





A view can be indexed by defining a UNIQUE
CLUSTERED index on it.
The CHECK OPTION prevents data in a view
from being modified such that the modified
data violates the view definition condition.
The SCHEMABINDING option is used to bind
the view to the schema of the base table.
Data can be modified through a view using
the UPDATE, INSERT and DELETE
statements.
For modifying data through a view, all
important columns in the base table must be
included in the view.
Database Objects/ Session 3/ 39 of 40