Using the MS-SQL Server Enterprise Manager Create View Wizard to Create a View

Views are virtual tables. Although they look and act like regular relational database tables, views contain no data. Rather, a view is a set of instructions for the DBMS that tells it what data stored in physical (real) tables to display and how to display it. MS-SQL Server gives you two ways to define a view. You can use the CREATE VIEW statement (which you will learn about in Tip 206, "Using a View to Display Columns in One or More Tables or Views"), or you can use the MS-SQL Server's Create View Wizard. Whichever method you use to create the view, the DBMS will store its name in the system tables along with the SELECT statement that lists the view's columns and search criteria (its WHERE clause).

At the lowest level, all views are based on one or more physical database tables. (You can create views based on other views. However, at some point, one of the views in the chain has to be based on an actual database table.) Therefore, to see how you can use the Create View Wizard to create a view, you must start by deciding on the data you want to display. Suppose, for example, that you want to create a view based on the data in the PRODUCTION table shown in Figure 65.1.

PRODUCTION table
Rep_ID	Call	Appointments	Sales	Deliveries
1	100	4	3	2
2	255	7	4	4
3	750	12	6	5
4	400	15	9	7
5	625	10	8	6
6	384	11	6	4
7	295	17	4	1

Figure 65.1: PRODUCTION table with sample data to use in creating a view

To use the Create View Wizard to create a view that displays data from a single table, perform the following steps:

1. Start the Enterprise Manager by clicking your mouse on the Start button. When Windows displays the Start menu, move your mouse pointer to Programs, select Microsoft SQL Server 7.0, and then click your mouse on Enterprise Manager.

2. To display the list of SQL servers, click your mouse on the plus (+) to the left of SQL Server Group.

3. Click your mouse on the icon for the SQL Server with the database in which you wish to create the view. For example, if you want to create a view in a database on a server named NVBIZNET2, click your mouse on the icon for NVBIZNET2.

4. Select the Tools menu Wizards option (or click your mouse on the Wizards button, the magic wand on the Standard Toolbar). Enterprise Manager will display the Select Wizard dialog box so that you can select the wizard you want to use.

5. Click your mouse on the plus (+) to the left of Database to display the list of database object wizards.

6. Click your mouse on Create View Wizard to select it, and then click your mouse on the OK button. Enterprise Manager will start the Create View Wizard, which displays its "Welcome to the Create View Wizard" screen.

7. Click your mouse on the Next button. The Create View Wizard will display the Select Database dialog box.

8. Click your mouse on the drop-down list button to the right of the Database Name field to display the list of databases on the SQL Server you selected in Step 3.

9. Click your mouse on the database in which you wish to create the view. For the current example, click your mouse on SQLTips to select the SQLTips database.

10. Click your mouse on the Next button. The Create View Wizard will display a Select Tables dialog box, similar to that shown in Figure 65.2.

    1. 

    2. Figure 65.2: The MS-SQL Server Create View Wizard's Select Tables dialog box

    3. Click your mouse on the check boxes of the tables whose data you want to include your view. For the current example, click your mouse on the check box for the PRODUCTION table until the check mark appears.

    4. Click your mouse on the Next button. The Create View Wizard will display a Select Columns dialog box, similar to that shown in Figure 65.3.

       

    5. Figure 65.3: The MS-SQL Server Create View Wizard's Select Columns dialog box

    6. Click your mouse on the check boxes of the columns you want to display. For the current example, select: PRODUCTION.REP_ID, PRODUCTION.CALLS, PRODUCTION. SALES, and PRODUCTION.DELIVERIES.

       Note

       The list of columns in the selection area of the Select Columns dialog box includes all of the columns for all of the tables selected in Step 11. The Create View Wizard shows you which columns belong in which tables by using the qualified column name for each column-that is, it displays the column name as . (where is the name of the table that contains ). Click your mouse on the Next button. The Create View Wizard will display the Define Restriction dialog box. If you do not want to display all of the rows in the tables you selected (in Step 11), enter the WHERE clause that you want the DBMS to use as the criteria for selecting the rows to display. For the current example, enter WHERE PRODUCTION.SALES > 4 to have the DBMS display only rows where the value in the SALES column of the PRODUCTION table is greater than 4. Click your mouse on the Next button. The Create View Wizard will display the Name the View dialog box. Enter a name for the view in the View Name field. For the current example, enter vw_sales_production into the View Name field. Note It is best to keep view names consistent so that you can distinguish them from actual tables when looking at a list of database objects. For example, if you start all of your view names (and only your view names) with "vw_", you will know that any database object starting with "vw_" is a view. Click your mouse on the Next button. The Create View Wizard will display the SQL statements the DBMS will use to create the view, in a Completing the Create View Wizard dialog box similar to that shown in Figure 65.4. Figure 65.4: The MS-SQL Server Create View Wizard's Completing the Create View Wizard dialog box Make any necessary corrections to the WHERE clause and any other changes necessary to further refine the view. (You can add or remove columns, change the view name, change the selection criteria in the WHERE clause, and so on.) Click your mouse on the Finish button. After you complete Step 19, the Create View Wizard will check the syntax of the statements in the Completing the Create View Wizard dialog box and will prompt you to correct any errors. (If there are any errors, the problem will most likely be with the WHERE clause you entered in Step 14.) Repeat Steps 18 and 19 until the Create View Wizard displays the "Wizard Complete!" message box, with the message "The view was created successfully." Once you see the message box, click your mouse on its OK button to return to the MS-SQL Server Enterprise Manager application window. Once the DBMS stores your view in its system tables, use the SELECT statement to display the view data. For example, to display all of the columns and rows in the view you created in the current example, execute the SELECT statement: SELECT * FROM vw_sales_production Given that the PRODUCTION table has the data shown in Figure 65.1, MS-SQL Server will display the virtual contents of your view table, VW_SALES_PRODUCTION as rep_id calls sales deliveries ----------- ------ ------ ---------- 3 750 6 5 4 400 9 7 5 625 8 6 6 384 6 4 (4 row(s) affected)