Think of a view as another table within the database. Views are often referred to as virtual tables since no physical table exists in the database: A view is merely a “stored query” that returns a result set that can be used in SQL. The view can then be used as part of other SQL queries as well as updates.

Views are not just a feature of SQL Server but are available in most modern relational database management systems (RDMS). I’ll use SQL Server 7.0/2000 for my examples because it’s so easy to manage views using the Enterprise Manager interface.

Enterprise Manager allows users with appropriate permissions to create, modify, and use views in their day-to-day tasks. Once you learn how to use views, you’ll wonder how you survived without them.

Managing views in SQL Server
Enterprise Manager provides an easy way to manage the views within your database. Figure A shows the Enterprise Manager application window.

Figure A

To see the views available in your database, expand the SQL Server Group you’re working with and drill down to the pubs that we’ll use. Select the Views icon (the pair of spectacles) in the left pane to display all the views available within that database in the right pane of the window, as shown in Figure B.

Figure B

To examine any of the views listed in the right pane, simply right-click on the view and select the Properties element of the menu. Examine the titleview view, which comes as part of the pubs database. The View Properties window will appear. It contains the SQL for that specific view, as shown in Figure C.

Figure C

To delete a view, simply right-click on the view in the right pane of the Enterprise Application manager and select the Delete item in the menu. It will prompt you with the dialog box shown in Figure D.

Figure D

To proceed with the deletion, select the Drop All button. The Show Dependencies button will display all objects on which this view depends (tables, views, etc.).

To rename a view, simply right-click on the view in the right pane and select the Rename item in the menu. The name of the view will be highlighted, and the new name can be typed in.

One important thing to note is the naming convention you choose for views. Whatever convention you use, at a minimum you should include the word view in the name. This intuitive naming convention will let the user community know they’re working with a virtual table and not an actual one; thus, they’ll know where to look for its definition.

Creating views
Creating a view is quite simple using the Enterprise Manager application with SQL. For instance, examine the following query, which will produce a list of employees (from the pubs database) along with their hire date:
SELECT fname AS FirstName,
   lname AS LastName, hire_date AS HireDate
FROM employee

Typing this query each time we want the list in this form can be quite tiresome. To save keystrokes, create a view called empview and reference the view using the following query:
SELECT * FROM empview

First you must drill down to the pubs database and right-click on the Views icon, which will display a menu. From the menu of items, choose New View. You should see a window similar to the one in Figure E containing four panes stacked on top of each other: Diagram Pane, Grid Pane, SQL Pane, and Results Pane.

Figure E

In the uppermost pane, the Diagram Pane, right-click and select the menu item labeled Add Table, which will bring up a dialog box similar to the one shown in Figure F.

Figure F

In this case, you’ll select the employee table, but notice that you can actually create a new view as well, by using an existing view. Simply select the Add button and close the dialog window. To create the empview example, select the columns you want to include in the view by checking the box to the left of each field. Notice that as you select each column, it appears in the pane second from the top. Rename each column by typing in the new name using the Alias column. Rename it as FirstName, LastName, and HireDate. Finally, in order to test the view, select the Exclamation icon in the menu bar above the panes to see what result set is returned. Figure G shows these steps.

Figure G

To save the view, just select the Save icon in the menu bar (the floppy disk), and it will display a dialog box asking for the name of the view. Give it the empview name. You can now reference this view from within a SQL window.

It’s easy to create a view that involves more than one table. Building on the empview example, you’ll also print out the employee’s job title, which will require you to join with the jobs table. Follow the steps to create a new view outlined in the previous example. Now add the employee and jobs tables to the view and close the dialog box. Notice that a many-to-one entity relationship is shown between the two tables. The only difference from the previous example is that you’ll now check the job_desc column in the jobs table and rename it to JobTitle. Figure H shows the view after you’ve finished.

Figure H

What’s your view?

Do you want to see more SQL Server content? Let us know what you want, or post a comment below.