This page has 0 threads | Add post

The View Designer

Written by Andy Brown

In this tutorial

Step 1 - Creating your view 2
Step 2 - Choosing your tables
Step 3 - Choosing your columns
Step 4 - Sorting your data (adding an ORDER BY clause)
Step 5 - Filtering (adding a WHERE clause)
Step 6 - Testing your view results
Step 7 - saving and closing your view
Step 8 -Showing the rows returned by a view

The easiest way to create a view is through the view designer. The rest of this page shows how to use this.

The view designer is a great way to start a query off and get your joins right, but if you want to link to tables in other databases, include complicated calculations in a view or add comments to it, you'll need to switch over to scripting your view (as described in the next part of this tutorial).

Step 1 - Creating your view 2

To do this, first choose to create a new view:

Right-click on the Views category and choose to create a new view.

You can now choose upon which tables the view should be based.

Step 2 - Choosing your tables

Choose which tables you want to include in your view:

Choose whether you're basing your view on tables, or on other views.
Choose the tables (you can use the CTRL key to select more than one table at the same time).
Click to Add the tables to your view.
When you've finished, click on the Close button to remove this Add Table dialog box.

These steps are shown in this diagram:

Add tables to your view by following the numbered steps above.

You can speed things up by double-clicking on each table that you want to include, then by pressing ESC to get rid of the dialog box.

If SQL Server knows that there is a relationship between the tables that you've added, it will show it:

Here there is a relationship between the films and studios table in the underlying database, so this appears in the view designer.

If the relationship didn't appear, you could add it in by clicking on a column in one table, and dragging it onto the corresponding column in the other table (then releasing the mouse button).

Step 3 - Choosing your columns

You can now tick the columns that you want to include in your view:

Here we've ticked 3 columns, to show the film name, the run time in minutes and the studio name. You can see that SQL Server is creating the SQL query for you at the bottom of the window.

If you want to give your columns aliases (alternative names), just type these in:

You don't have to type the square brackets - SQL will add these for you.

Step 4 - Sorting your data (adding an ORDER BY clause)

To sort data, just choose Ascending or Descending for any column:

Here we're sorting by the studio name, then by the film name (the numbers 1 and 2 give the priority order for sorting).

Step 5 - Filtering (adding a WHERE clause)

You can type in any valid SQL criteria in the Filter columns:

Here we're showing all films lasting less than 2 hours.

Step 6 - Testing your view results

If you want to see what rows your view would return, click on the ! button:

Click on this red exclamation mark tool to run your view and see the results.

You should now be able to see the rows returned by this view at the bottom of your window:

This view has returned 111 rows. The view window is split into 4 parts.

If you can't see the results, it might be that they're not visible. You can hide or display the 4 parts of the view window listed above using these 4 tools: