Create and Alter Views

• Create and alter database objects. Objects include views.

This section looks at creating views, including how to restrict the data that is returned through the view, and how to enhance these views by using aggregates and joins. Finally, it explains how to modify views and remove views. Because it is best to start at the beginning, the first thing to study is how to create views.

Creating Views

Views can be created using the Enterprise Manager, the Create View Wizard, or using T-SQL within the SQL Query Analyzer. When you use the Query Analyzer, you use the CREATE VIEW statement to create your virtual table. When creating a view, remember these guidelines. The following shows the syntax for the CREATE VIEW statement.

 CREATE VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ]   [ WITH < view_attribute > [ ,...n ] ]   AS   < SELECT_statement >     [ WITH CHECK OPTION ] < view_attribute > ::=   { ENCRYPTION  SCHEMABINDING  VIEW_METADATA } 

• view_name is the name of the view and must adhere to the rules for identifiers.

• column is the name to be used for the columns that are returned from the select statement. This is useful when you want to change the name of the column identifier.

• WITH ENCRYPTION encrypts the syscomments columns that contain the text of the CREATE VIEW statement. Encryption prevents people from being able to see the view definition, replicate the view, or generate a complete database creation script. If you attempt to view the properties of the view through Enterprise Manage or sp_helptext , you see a comment telling you that the view is encrypted. If you query sys_comments directly, you see an encrypted string or gibberish.

• WITH SCHEMABINDING specifies that the view be bound to the schema. Visually this means that tables, views, and functions that the view refers to must contain the owner name in the reference (for example, dbo.sales ). Schema binding is designed to prevent objects from being dropped or modified while the view still refers to them, because that would cause the view to cease functioning.

NOTE

Indexed Views and SCHEMABINDING Note that SCHEMABINDING has to be specified when you create indexed views. Also, when SCHEMABINDING is specified, you have to adhere to the owner.object syntax when referencing tables or views in the creation of your view. Indexed views are covered in Chapter 10, "Boosting Performance with Indexes."

When creating a view, you may want to name an alias for a certain column to make a better user interface for your users. However, it is not obligatory . If a column alias is not specified, the view refers to the column name in the underlying table or view referenced. You must assign a column alias when joining two tables that have the same column names .

Selecting Specific Columns and Rows

As you have noted in previous sections, views are effective for securing data and reducing the display of non-relevant data. Views can help in showing your users only what you want them to see. This may be a set of rows or columns or both. This is especially important when sensitive information is involved, such as salary and credit card information. Views allow you to retrieve only the data that is needed or relevant.

At a particular point in time, you may want to limit your view data to specific rows or columns of data. The following two sections look at selecting columns for your view and selecting rows for your view. The actual requirement for which data should be shown depends on your specific circumstances.

Selecting Specific Columns

You can define a view that is made up of some, or all, of the columns of a table. This process is sometimes known as vertically filtering a table (see Figure 7.1). Selecting certain columns is useful when you want to protect data from prying eyes. A good example of this might be when you want to hide a salary column of a table, and therefore create a view that references all columns except the salary column. Step by Step 7.1 shows you how to specify columns with the Enterprise Manager.

Figure 7.1. A sample view filtered by column.

STEP BY STEP

7.1 Creating Views by Selecting Specific Columns Using the Enterprise Manager

1. Open the Enterprise Manager from the Start menu and connect to the default instance of SQL Server.

2. To create a new view in the Pubs database, expand Pubs from the right pane and then right-click the Views icon. Select New View to start designing a new view.

3. This example uses the Sales table as a base table for the new view and you will design it so that it selects all columns from the Sales table except for the Payterms column. To do this, right-click in the topmost pane and select Add Table; this initializes the Add Table dialog box. Scroll down the list to find Sales; click Add, and then click Close to close the Add Table dialog box. Notice that the table was added graphically on the top-most pane.

4. To add a new column to the view definition, select the check box next to it. For this example, select all columns except Payterms . Adding a column using this graphical method automates coding (see for yourself by looking at the code pane) and, thus, is a good solution for a user who is an SQL programming novice. This is shown in Figure 7.2.

   Figure 7.2. Automated coding using the View Designer.

   

5. After you have selected the desired columns to be referenced in your view, you can take it on a test run by clicking on the red exclamation mark on the toolbar.

6. You can now save your view by clicking on the disk button in the toolbar. Save the view with an appropriate name, such as SalesViews , and then you are finished.

   You can also specify desired columns using the Query Analyzer. Examine the following code:

    CREATE VIEW SelectedColumns AS   SELECT Type, Price     FROM Titles 

Now that you have seen how to restrict data in a view by columns, you now see how to limit the number of rows that are returned.

Selecting Specific Rows

You can also define a view that references some, or all, of the rows of a table. Selecting certain rows involves the help of the WHERE clause. This filters out all entries that do not match the conditions defined in a WHERE clause (see Figure 7.3), and is known as horizontal filtration. Using the WHERE clause returns only relevant data and forces data security through the restricting of the returned data.

Figure 7.3. A sample view filtered by row.

With horizontal filtration, the types of attributes (or columns) returned in the view remains constant, but you can see the difference in the number of rows returned. This is like pulling out a few pages of a phone book. Each entry in a phone book has a name, address, and phone number (the columns), but the few pages that were pulled out represent the portion of the data that you are going to use. This is how a horizontally filtered view behaves, with all the columns, but not all the rows. To create a view based on horizontal filtration, see this code:

 CREATE VIEW SelectedRows AS   SELECT Au_ID, State, au_FName     FROM Authors     WHERE State = 'CA' 

The following query shows you what horizontal filtering looks like:

 SELECT * FROM SelectedRows 

After executing this statement, you should notice that the State , FirstName , and Au_ID columns are shown WHERE the State is CA . This is shown in Figure 7.4.

Figure 7.4. Creating a row-based filtration on a view.

You should now have a good grasp of the basics of a view. You know how to restrict the amount of data that is returned through the selection of columns and rows. The next section looks at further controlling the data that is returned through the view.

Accessing Views

Displaying the definition of a view may need to be done at some time. To access the exact definition of the view you created, use the sp_helptext system stored procedure. The actual definition of a view is stored in the syscomments system table. The sp_helptext procedure queries the syscomments , organizes the information required, and displays the view definition. As you've already seen, protecting a view definition is possible if you use the WITH ENCRYPTION option. sp_helptext lets you know what tables and views your view references. This information is helpful if you are having trouble figuring out why your view does not work because you will see what tables or views your malfunctioning view uses.

Knowing the actual structure of the tables that your view references is useful when troubleshooting or redesigning your view; but knowing what views or stored procedures reference your view is useful when you plan to make alterations to or drop a view. If you do not check on known dependencies, then you risk making other views or stored procedures unusable. To see what objects reference a view, use the sp_depends stored procedure. View dependencies are stored in the sysdepends system table. When the sp_depends stored procedure is executed, it queries the sysdepends table and summarizes the final result. Step by Step 7.2 shows you how to access views.

STEP BY STEP

7.2 Accessing Views

1. Open the Query Analyzer by selecting it from the Start menu and logging in.

2. To view the definition of titleview from the Pubs database (see Figure 7.5), execute the following:

    USE pubs GO EXEC sp_helptext titleview 

   Figure 7.5. Accessing the definition of a view.

   

3. To display the view's associated tables, execute the following query. This process is shown in Figure 7.6.

    EXEC sp_depends titleview 

   Figure 7.6. Accessing the dependencies of a view.

   

Enhancing Views

You can create view definitions beyond mundane SELECT statements that simply place data statements into a resultset. With views, you can use joins to get data from multiple tables. This is useful because the desired data is not always stored on a single table (in fact, this is rarely the case). You can also leverage the complexity of aggregate functions in your views in the same way you would use them on a normal table. You can further refine data retrieval by building views on views, so that data is filtered on the appropriate conditions.

Aggregates and Views

Aggregates offer a great deal of calculation power and views can make use of them. These aggregates include AVG , COUNT , DISTINCT , and similar functions. Leveraging the power of aggregates enables you to create useful reports that can be produced based on data in a table.

The following example outlines the importance of aggregates with the example of a user who requests to see a report on the minimum, maximum, average and total values of the Quantity column (grouped by orderID) from the Order Details table in the Northwind database. Running the following code produces a view named QuantityAnalysis :

 CREATE VIEW QuantityAnalysis AS   SELECT OrderID, MIN (Quantity) AS minimum, MAX (Quantity) AS maximum,          AVG (Quantity) as Average, SUM (Quantity) AS Total   FROM [Order Details]   GROUP BY Order_ID 

Now, to see the results that this query produces, find out what the view will produce. To do this, execute the following query, as shown in Figure 7.7:

 SELECT *   FROM QuantityAnalysis 

Figure 7.7. Reporting summary data using aggregates in views.

The summary or statistical features of aggregates are not the only way to enhance a view; you can also join tables to consolidate information.

Joins and Views

Ever wondered how to gather information from multiple tables to form a view definition? This can be accomplished with a join.

Implementing joins within your views enables you to consolidate related data that may be scattered around your database. When you incorporate joins into your view, you are literally assembling information from diverse tables, but not actually storing data on the view, because the view does not contain the actual data. Joins are used to display data from multiple tables in a single resultset. They help in the management of your data by saving the join definition, thereby releasing the user from needing to know how to construct the join.

A resultset may use joins to gather data from a number of tablessay two, three, or four. You can reference up to 256 tables in a single SELECT statement or view. Take the Pubs database, for instance. To find out the royalty rate of an author, you can't just query the Authors table, because it doesn't store adequate information; you have to join two tables. The purpose of a resultset is to gather information into a single pot. To create a view that joins data, follow Step by Step 7.3.

STEP BY STEP

7.3 Creating a View That Incorporates Joins

1. Open the Query Analyzer by selecting it from the Start menu. Log in with the appropriate credentials.

2. In this example, you create a view that holds the author's name from the Authors table as well as the titles written by that author from the Titles table. The only way to do this is by creating a multi-join, first by creating a join to the TitleAuthor table and then a second join to the Titles table. To create a multi-join view that joins the Authors table, TitleAuthor table, and Titles table, exe cute the following:

    CREATE VIEW AuthorSummaryInfo AS   SELECT Authors.au_fname, Authors.State, title     FROM Authors     JOIN titleauthor ON Authors.au_ID = TitleAuthor.Au_ID     JOIN titles on titleauthor. Title_ID = Titles.Title_ID 

3. To see the view you created, run the following as shown here and in Figure 7.8:

    SELECT * FROM AuthorSummaryInfo 

   Figure 7.8. Incorporating joins in views.

   

Joins enable views to act as data consolidation points. Next, you see how to re-filter the data by creating a view based on another view.

Views on Views

Similar to the way a table serves as the base for a view, a view can gather its information from another view. Creating a view using an existing view as the underlying information source helps when you want to further refine criteria on an existing view. To create a view referencing a base view, examine the following code listing. This creates a view called AuthorsView that includes information on all authors, regardless of the states they live in. The next view, AuthorsCA , creates a view that references AuthorsView and selects only those authors from California:

 CREATE VIEW AuthorsView AS   SELECT Au_fname, State, Au_lname     FROM Authors GO CREATE VIEW AuthorsCA AS   SELECT Au_fname, State     FROM AuthorsView     WHERE State = 'CA' 

To display the newly created view, query it using

 SELECT *   FROM AuthorsCA 

You should notice that only the authors living in California are listed. If the authors in California represent a piece of information that is regularly required, you now have an easy way to extract that data from a view that was already useful.

You should now have a clear understanding of how to enhance a view by using the summary power of aggregates, the data consolidation of joins, and the refined filtering of basing a view on another view. If you do not need advanced view features, you can make use of the Create View Wizard.

Creating Views with the Wizard

If you are in a hurry and just want a quick view with all the basic elements, you can use the Create View Wizard. This is a five-step process that takes only a few seconds to complete.

EXAM TIP

Easily Creating Views Views can also be easily created using the Create View Wizard. This is useful to know when creating views for personal use, but the exam will not test you on this process.

Before you can use the wizard, you have to open it. You will find all the wizards in Enterprise Manager in the Tools Menu. The Create View Wizard is in the Database section, as shown in Figure 7.9. Select it and choose OK.

Figure 7.9. The Create View Wizard is accessible through the Wizards dialog.

The Welcome screen for the wizard shows you what to expect during the wizard. This welcome screen is shown in Figure 7.10.

Figure 7.10. The Create View Wizard is a simple five-step wizard.

The five steps to the Create View Wizard are

1. Select the database that will be used for the view.

2. Select one or more tables for use in the view.

3. Select one or more columns from the selected tables.

4. Type a WHERE clause to restrict the returned rows.

5. Name the new view.

The wizard provides a way of specifying advanced options, such as ENCRYPTION or SCHEMABINDING , but does simplify the view creation process.

Altering Views

SQL Server accommodates the need to adjust a view, whether it is renaming or completely changing a view definition. This flexibility is provided via the sp_rename stored procedure and the ALTER VIEW statement. The following sections describe these two in greater depth.

NOTE

Changing Object Names The sp_rename system procedure is used to rename any database object in your database, so it's one you should remember.

Renaming a View

Renaming a view, or any object for that matter, is done with the sp_rename system stored procedure.

 sp_rename [ @objname = ] 'object_name' ,     [ @newname = ] 'new_name'     [ , [ @objtype = ] 'object_type' ] 

To use this procedure, first supply the original name followed by a comma and then type in the new name of the view. Because this system stored procedure is extremely generic and used for a lot of objects, you have to specifically specify OBJECT as object_type . Views, triggers, constraints, and stored procedures all belong in the category OBJECT . Renaming views and stored procedures causes the sysobjects system table to be updated. For example, to rename a view in the Pubs database you could use:

NOTE

The Missing 'OBJECT' In most cases, you can omit the @objtype if it is going to be 'OBJECT' .

 use pubs GO sp_rename 'titleview','oldtitleview', 'OBJECT' 

Use a great deal of care when renaming objects because any scripts, stored procedures, or objects that refer to the renamed object by its old name will fail to work.

Editing a View

You may need to change the way a view accesses data, known as the view definition . When doing this, there must already be a previously created view using only the CREATE VIEW statement. You can also drop and re-create the view, but doing so resets previously granted permissions. Using the ALTER VIEW statement, you can easily reshape the definition of a view without affecting permissions granted. The syntax for ALTER VIEW is as follows :

 ALTER VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ] [ WITH < view_attribute > [ ,...n ] ] AS     < select_statement > [ WITH CHECK OPTION ] < view_attribute > ::=     { ENCRYPTION  SCHEMABINDING  VIEW_METADATA } 

The syntax is relatively similar to CREATE VIEW . view_name is the name of the view being altered . The WITH ENCRYPTION clause protects the definition of your view. You encrypt the definition of your view because you may not want users to display it, to protect your design from duplication. Encrypting your view using WITH ENCRYPTION ensures that no one can display your view, whether using sp_helptext , viewing it through the Enterprise Manager, or generating it through a Database Creation Script.

If your original view was created with WITH ENCRYPTION or WITH SCHEMABINDING , then your ALTER VIEW statement must include the same options. To alter a view using ALTER VIEW , see Step by Step 7.4

STEP BY STEP

7.4 Altering a View Using the ALTER VIEW Statement

1. Open the SQL Server Query Analyzer by selecting it from the Start menu.

2. Before altering the definition of a view, there must already be an existing view. Run the following query to create a new view for the purposes of this example:

    USE pubs GO CREATE VIEW EmployeeView AS   SELECT emp_id, fname, lname, job_lvl     FROM Employee     WHERE fname LIKE ' p%' 

3. To change the definition of the view you created in Step 2, you need the help of the ALTER VIEW statement. Run the following query:

    ALTER VIEW EmployeeView AS   SELECT emp_id, fname, lname, job_lvl     FROM Employee     WHERE job_lvl <150 

WARNING

Don't Be Careless with Changes Altering views or tables that are used by views may cause the dependent views to stop working. SQL Server does not back-check all table or view alterations to ensure they do not create errors. This is where the SCHEMABINDING option helps out. If you have SCHEMA -bound objects, then you cannot ALTER the source objects at all. It may be preferable to drop and recreate a series of objects, rather than have objects that do not function. This means more planning.

You may find that SQL Server does not allow you to perform tasks because of one dependency or another. For instance, all objects are dependent on their owner, so you are not able to drop a user from the database if they own objects. The number of database objects that the server checks for dependencies is very large, but it is easy to find out what objects they are. A quick query of sysobjects can reveal any object ownership dependencies.

Scripts, stored procedures, and views can exist anywhere , and refer to tables or views in your database. There is no easy location that can be checked that tells SQL Server who is referencing your table or view. Another user can reference your view from an entirely different server, without your knowledge, if that user has been granted the SELECT permission to it. Therefore, there is no back-checking of integrity when you change the structure of tables or viewswho knows who might be using it? When examining the situation from this perspective, it is easy to see why SQL Server does not check; but it is still surprising that it does not check. Take for example the following script:

 USE pubs GO CREATE VIEW AuthorInfo AS   SELECT au_id, au_fname, au_lname, city, zip     FROM dbo.Authors GO CREATE VIEW AuthorShortInfo AS   SELECT au_id, au_fname, city     FROM dbo.AuthorInfo GO ALTER VIEW AuthorInfo AS   SELECT au_id, au_fname, au_lname, zip     FROM dbo.Authors GO SELECT * FROM AuthorShortInfo 

Running this script would yield the following error messages in Query Analyzer:

 Server: Msg 207, Level 16, State 3, Procedure AuthorShortInfo, Line 2 Invalid column name 'city'. Server: Msg 4413, Level 16, State 1, Line 1 Could not use view or function 'AuthorShortInfo' because of binding errors. 

This error states that a required object (the 'city' column) no longer exists. This problem is possible, and can easily occur, because of the lack of checking on changes to source objects. This same error message would result if you execute sp_refreshview on AuthorShortInfo , and sp_refreshview would return a non-zero value because of the failure. It is possible to create a small procedure to query sysobjects for views, loop them through them, and refresh each view to ensure that they are all still valid.

Dropping Views

To remove a view from a database, use the DROP VIEW statement. Dropping a view removes the definition of the view from the database and an entry in the sysobjects while not affecting the underlying tables and views.

The DROP VIEW command is relatively simple as shown here:

 DROP VIEW { view } [ ,...n ] 

To drop a view named my_view , you simply have to execute DROP VIEW 'my_view' . With the DROP VIEW command you may also choose to remove a number of views at the same time. To do this, specify each view in a comma-delimited list. Dropping an indexed view leads to the removal of all indexes associated with that view. To drop the view created in Step by Step 7.4, enter and run the following code:

WARNING

Dropping Used Views Dropping views that are used by other views causes the dependent views to stop working, because the object that they refer to no longer exists.

 DROP VIEW EmployeeView GO 

If you drop a view that is used by another object, such as a view, you cause problems with the dependent view. For example, examine the following code:

 CREATE VIEW auth1 AS   SELECT au_fname, au_lname, city, state     FROM pubs.dbo.authors GO CREATE VIEW auth2 AS   SELECT au_fname, au_lname     FROM pubs.dbo.auth1 GO DROP VIEW auth1 GO SELECT * FROM auth2 GO 

When this code is executed, you get the following error message:

 Server: Msg 208, Level 16, State 1, Procedure auth2, Line 3 Invalid object name 'pubs.dbo.auth1'. Server: Msg 4413, Level 16, State 1, Line 1 Could not use view or function 'auth2' because of binding errors. 

This error message lets you know that the dependent view 'pubs.dbo.auth1' is not present in the database.