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.
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 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 definition of a view can be encrypted to ensure that its definition cannot be obtained by anyone, including the owner of the view.
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.
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
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:
In addition to the previous restrictions, the SELECT statement in the view cannot contain the following Transact-SQL syntax elements:
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:
Indexes are discussed in more detail in Chapter 11, "Implementing Indexes."
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:
To create distributed partitioned views for the preceding example, you should take the following steps:
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
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:
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.
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
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.
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.
A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.
Notice that the dbo.BookAuthorView node contains several nodes (including the Columns node and the Indexes node).
Notice that the three columns that were included in the SELECT query of the CREATE VIEW definition are included in this node.
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
In this statement, you are adding the TitleID column to the SELECT statement. The result set will now include the additional column.
A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.
Notice that the TitleID column has been added to the list of columns.
USE BookShopDB GO DROP VIEW BookAuthorView
In this statement, you are deleting the BookAuthorView view from the BookShopDB database.
A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.
Notice that the BookAuthorView view no longer appears.
NOTE
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.