Lesson 2:Creating, Modifying, and Deleting Views

3 4

As you learned in Lesson 1, a view is simply a SELECT query saved in the database. You can create a view as you would many other objects in a database, and once that view is created, you can modify the view or delete it. In this lesson, you will learn how to create views (including indexed views and partitioned views), modify those views, and then delete them.


After this lesson, you will be able to:

  • Create basic views, indexed views, and partitioned views.
  • Modify and delete views.

Estimated lesson time: 35 minutes


Creating Views

You can create views only in the current database. The tables and views referenced by the new view can exist in other databases or even in other servers, however, if the view is defined with distributed queries. View names must follow the rules for identifiers and must be unique for each user. Additionally, the name must not be the same as any tables owned by that user. You can build views on other views and on procedures that reference views. SQL Server 2000 enables views to be nested up to 32 levels.

When creating a view, you must adhere to the following restrictions:

  • You cannot associate rules or DEFAULT definitions with views.
  • You cannot associate AFTER triggers with views, only INSTEAD OF triggers.
  • The query that defines the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
  • You cannot define full-text index definitions on views.
  • You cannot create temporary views, and you cannot create views on temporary tables.
  • Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views with schema binding will fail if these statements affect the view definition.
  • You cannot issue full-text queries against a view, although a view definition can include a full-text query if the query references a table that has been configured for full-text indexing.

You must specify the name of every column in the view if any of the columns in the view are derived from an arithmetic expression, a built-in function, a constant, or if two or more of the columns in the view would otherwise have the same name (usually because the view definition includes a join and the columns from two or more different tables have the same name).

In addition, you must specify the name of every column in the view if you want to give any column in the view a name that is different from the column from which it is derived. (You can also rename columns in the view.) A view column inherits the data type of the column from which it is derived, whether or not you rename it. Note that this restriction does not apply if a view is based on a query containing an outer join, because columns might change from not allowing null values to allowing them. Otherwise, you do not need to specify column names when creating the view. SQL Server gives the columns of the view the same names and data types as the columns in the query that defines the view. The select list can be a full or partial list of the column names in the base tables.

To create a view, you must be granted permission to do so by the database owner, and you must have appropriate permissions for any tables or views referenced in the view definition.

By default, as rows are added or updated through a view, they disappear from the scope of the view when they no longer fall into the criteria of the query that defines the view. For example, a view can be defined that retrieves all rows in which the employee's salary is less than $30,000. If an employee's salary is increased to $32,000, then querying the view no longer displays that particular employee because the salary does not conform to the criteria set by the view. However, the WITH CHECK OPTION clause forces all data modification statements executed against the view to adhere to the criteria set within the SELECT statement that defines the view. If you use this clause, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this situation to happen is canceled, and an error is displayed.

NOTE


The WITH CHECK OPTION on a view does not affect the underlying tables when you modify those tables directly. Only the updates that are made through the view itself are affected by WITH CHECK OPTION.

The definition of a view can be encrypted to ensure that its definition cannot be obtained by anyone, including the owner of the view.

Creating Standard Views

You can use Enterprise Manager to create a view, or you can use the CREATE VIEW statement in Transact-SQL. In the following example, a CREATE VIEW statement is used to define a SELECT query that returns data from the Northwind database:

 USE Northwind GO CREATE VIEW CustomerOrders AS SELECT o.OrderID, c.CompanyName, c.ContactName   FROM Orders o JOIN Customers c   ON o.CustomerID = c.CustomerID 

In this statement, you are creating the CustomerOrders view in the Northwind database. The view uses a SELECT statement to select the order ID from the Orders table and the company name and contact name from the Customers table. A join connects the two tables.

Creating Indexed Views

Views are also known as virtual tables because the result set returned by the view has the same general form as a table (with columns and rows), and views can be referenced like tables in SQL statements. The result set of a standard view is not stored permanently in the database. Each time a query references the view, SQL Server 2000 dynamically merges the logic needed to build the view result set into the logic needed to build the complete query result set from the data in the base tables. The process of building the view result is called materializing the view.

For a standard view, the overhead of dynamically building the result set for each query that references the view can be substantial if the view involves complex processing of large numbers of rows (such as aggregating large amounts of data) or joining many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way that a table with a clustered index is stored.

NOTE


You can create indexed views only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.

Another benefit of creating an index on a view is that the 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.

Creating a clustered index on a view stores the data as it exists at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way as an index created on a base table. As modifications are made to the data in the base tables, they are also reflected in the data stored in the indexed view. Because the view's clustered index must be unique, SQL Server can more efficiently find the index rows affected by any data modification.

Indexed views can be more complex to maintain than indexes on base tables. You should create an index on a view only if the improved speed in retrieving the result outweighs the increased overhead of making modifications. This improvement usually occurs for views that are mapped over relatively static data, process many rows, and are referenced by many queries.

A view must meet the following requirements before you can create a clustered index on it:

  • The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this setting for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.
  • The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
  • The view must not reference any other views, only base tables.
  • All base tables referenced by the view must be in the same database as the view and must have the same owner as the view.
  • The view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables.
  • User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
  • Tables and user-defined functions must be referenced by two-part names. One-part, three-part, and four-part names are not allowed.
  • All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic.
  • If GROUP BY is not specified, the view select list cannot contain aggregate expressions.
  • If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.
  • A column resulting from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view or table.

In addition to the previous restrictions, the SELECT statement in the view cannot contain the following Transact-SQL syntax elements:

  • The asterisk ( * ) syntax to specify columns in a select list
  • A table column name used as a simple expression that is specified in more than one view column
  • A derived table
  • Rowset functions
  • A UNION operator
  • Subqueries
  • Outer or self joins
  • The TOP clause
  • The ORDER BY clause
  • The DISTINCT keyword
  • COUNT(*) (COUNT_BIG(*) is allowed.)
  • The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions
  • A SUM function that references a nullable expression
  • The full-text predicates CONTAINS or FREETEXT
  • The COMPUTE or COMPUTE BY clauses

Creating the Index

After you create a view, you can create an index on that view, assuming that the view adheres to the requirements for an indexed view. The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional non-clustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with the view name.

The CREATE INDEX statement must meet the following requirements in addition to the normal CREATE INDEX requirements:

  • The user executing the CREATE INDEX statement must be the view's owner.
  • The NUMERIC_ROUNDABORT option must be set to OFF.
  • The view cannot include text, ntext, or image columns (even if they are not referenced in the CREATE INDEX statement).
  • If the SELECT statement in the view definition specifies a GROUP BY clause, the key of the unique clustered index can reference only columns specified in the GROUP BY clause.
  • The following SET options must be set to ON when the CREATE INDEX statement is executed:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIERS

Indexes are discussed in more detail in Chapter 11, "Implementing Indexes."

Creating Partitioned Views

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. SQL Server 2000 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. In a distributed partitioned view, at least one of the participating tables resides on a different (remote) server. In addition, SQL Server 2000 differentiates between partitioned views that are updateable and views that are read-only copies of the underlying tables.

Distributed partitioned views can be used to implement a federation of database servers. A federation is a group of servers that is administered independently but that cooperates 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, multi-tiered Web sites.

Before implementing a partitioned view, you must first partition a table horizontally. The original table is replaced with several smaller member tables. Each member table has the same number of columns as the original table, and each column has the same attributes (such as data type, size, and collation) as the corresponding column in the original table. If you are creating a distributed partitioned view, each member table is on a separate member server. For the greatest location transparency, the name of the member databases should be the same on each member server, although this setup is not a requirement (for example, Server1.CustomerDB, Server2.CustomerDB, and Server3.CustomerDB).

You should design the member tables so that each table stores a horizontal slice of the original table, based on a range of key values. The ranges are based on the data values in a partitioning column. The range of values in each member table is enforced by a CHECK constraint on the partitioning column, and ranges cannot overlap. For example, you cannot have one table with a range from 1 through 200,000 and another with a range from 150,000 through 300,000, because it would not be clear which table contains the values from 150,000 through 200,000.

For example, suppose you are partitioning a Customer table into three tables. The CHECK constraint for these tables should be defined as follows:

 -- On Server1: CREATE TABLE Customer_33 (CustomerID INTEGER PRIMARY KEY       CHECK (CustomerID BETWEEN 1 AND 32999),       ... -- Additional column definitions) -- On Server2: CREATE TABLE Customer_66 (CustomerID INTEGER PRIMARY KEY       CHECK (CustomerID BETWEEN 33000 AND 65999),       ... -- Additional column definitions) -- On Server3: CREATE TABLE Customer_99 (CustomerID INTEGER PRIMARY KEY       CHECK (CustomerID BETWEEN 66000 AND 99999),       ... -- Additional column definitions) 

After creating the member tables, define a distributed partitioned view on each member server, with each view having the same name. This setup lets queries referencing the distributed partitioned view name run on any of the member servers. The system operates as if a copy of the original table is on each member server, but each server has only a member table and a distributed partitioned view. The location of the data is transparent to the application.

You can build the distributed partitioned views by taking the following steps:

  1. Add linked server definitions on each member server containing the connection information needed to execute distributed queries on the other member servers. This action gives a distributed partitioned view access to data on the other servers.
  2. Set the lazy schema validation option (by using sp_serveroption) for each linked server definition used in distributed partitioned views. This setting optimizes performance by ensuring that the query processor does not request metadata for any of the linked tables until data is actually needed from the remote member table.
  3. Create a distributed partitioned view on each member server. The views should use distributed SELECT statements to access data from the linked member server and merge the distributed rows with rows from the local member table.

To create distributed partitioned views for the preceding example, you should take the following steps:

  1. Add a linked server definition named Server2 with the connection information for Server2 and a linked server definition named Server3 for access to Server3.
  2. Create the following distributed partitioned view:
 CREATE VIEW Customers AS    SELECT *    FROM CompanyDatabase.TableOwner.Customers_33 UNION ALL    SELECT *    FROM Server2.CompanyDatabase.TableOwner.Customers_66 UNION ALL    SELECT *    FROM Server3.CompanyDatabase.TableOwner.Customers_99 
  1. Perform the same steps on Server2 and Server3.

Modifying Views

After a view is defined, you can change its name or modify its definition without dropping and recreating the view, which would cause it to lose its associated permissions. When you rename a view, adhere to the following guidelines:

  • The view to be renamed must be in the current database.
  • The new name must follow the rules for identifiers.
  • You can rename only views that you own.
  • The database owner can change the name of any user's view.

Altering a view does not affect any dependent objects (such as stored procedures or triggers) unless the definition of the view changes in such a way that the dependent object is no longer valid.

To modify a view, you can use Enterprise Manager or the Transact-SQL ALTER VIEW statement to update the SELECT query, as shown in the following example:

 ALTER VIEW CustomerOrders AS SELECT o.OrderID, o.OrderDate, c.CompanyName, c.ContactName FROM Orders o JOIN Customers c   ON o.CustomerID = c.CustomerID 

In this statement, you are modifying the select list so that it includes the order date. The SELECT query in the ALTER VIEW statement replaces the SELECT query that was defined in the original CREATE VIEW statement.

In addition, you can modify a view to encrypt its definition or to ensure that all data modification statements executed against the view adhere to the criteria set within the SELECT statement that defines the view.

Deleting Views

After a view has been created, you can delete the view if it is not needed or if you want to clear the view definition and the permissions associated with it. When a view is deleted, the tables and the data upon which it is based are not affected. Any queries that use objects that depend on the deleted view fail when they are next executed (unless a view with the same name is created). If the new view does not reference objects expected by any objects dependent on the new view, however, queries using the dependent objects will fail when executed.

For example, suppose you create a view named MyView that retrieves all columns from the Authors table in the Pubs database, and this view is deleted and replaced by a new view named MyView that retrieves all columns from the Titles table instead. Any stored procedures that reference columns from the underlying Authors table in MyView now fail because those columns are replaced by columns from the Titles table instead.

To delete a view, you can use Enterprise Manager or the Transact-SQL DROP VIEW statement, as shown in the following example:

 DROP VIEW CustomerOrders 

Exercise 1:  Creating and Modifying a View

In this exercise, you will use the CREATE VIEW statement to create a view in the BookShopDB database, the ALTER VIEW statement to modify this view, and the DROP VIEW statement to delete the view from the database. To perform this exercise, you should be logged onto your Windows 2000 Server computer as Administrator.

To create the BookAuthorView view in the BookShopDB database

  1. Open Query Analyzer and connect to your local server.
  2. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB GO CREATE VIEW BookAuthorView AS SELECT a.FirstName, a.LastName, b.Title  FROM Authors a JOIN BookAuthors ba   ON a.AuthorID = ba.AuthorID   JOIN Books b   ON ba.TitleID = b.TitleID 

In this statement, you are defining the BookAuthorView view in the BookShopDB database. The CREATE VIEW statement contains a SELECT query that joins the Authors table to the BookAuthors table and the BookAuthors table to the Books table. The result set from this query will include the author's first and last names and the books that this author has written.

  1. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

  1. If the Object Browser window is not displayed, open it now.
  2. In the object tree of the Object Browser window, locate the new view that you created.

Notice that the dbo.BookAuthorView node contains several nodes (including the Columns node and the Indexes node).

  1. Expand the Columns node.

Notice that the three columns that were included in the SELECT query of the CREATE VIEW definition are included in this node.

To modify the BookAuthorView view in the BookShopDB database

  1. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB GO ALTER VIEW BookAuthorView AS SELECT a.FirstName, a.LastName, b.TitleID, b.Title  FROM Authors a JOIN BookAuthors ba   ON a.AuthorID = ba.AuthorID   JOIN Books b   ON ba.TitleID = b.TitleID 

TIP


You can copy and paste the code from the previous procedure and modify it to contain an ALTER VIEW statement and the TitleID column.

In this statement, you are adding the TitleID column to the SELECT statement. The result set will now include the additional column.

  1. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

  1. In the object tree of the Object Browser window, locate the BookAuthorView view and expand the Columns node.

Notice that the TitleID column has been added to the list of columns.

  1. Close Query Analyzer.

To delete the BookAuthorView view from the BookShopDB database

  1. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB GO DROP VIEW BookAuthorView 

In this statement, you are deleting the BookAuthorView view from the BookShopDB database.

  1. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

  1. In the object tree of the Object Browser window, locate the Views node for the BookShopDB database.

Notice that the BookAuthorView view no longer appears.

NOTE


If the BookAuthorView view is still listed in the object tree, you will need to refresh the screen to update the listings.

Lesson Summary

You can create a view as you would many other objects in a database, and once that view is created, you can modify the view or delete it. When creating a view, you must adhere to specific restrictions. You can use Enterprise Manager to create a view, or you can use the CREATE VIEW statement in Transact-SQL. In addition to standard views, you can create indexed views. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way as a table with a clustered index is stored. The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional non-clustered indexes. In addition to indexed views, you can create partitioned views. 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. After a view is defined, you can change its name or modify its definition without dropping and recreating the view, which would cause it to lose its associated permissions. You can also delete a view if it is not needed or if you want to clear the view definition and the permissions associated with it. When a view is deleted, the tables and the data upon which it is based are not affected.



Microsoft Press Staff - MCSE. Microsoft SQL Server 2000 Database Design and Implementation Training Kit
MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)
ISBN: 073561248X
EAN: 2147483647
Year: 2001
Pages: 97

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net