BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Views are queries that you can save and reuse; but you can also use them as a basis for further queries. They do have their foibles too, as this blog explores.
- Views in SQL Server
- The View Designer (this blog)
- Scripting views using SQL
- The Pros of using Views in SQL Server
- The Disadvantages of Views
Posted by Andy Brown on 17 December 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
The View Designer
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 blog).
Step 1 - Creating your view
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:
These 4 tools hide or display:
However, it's probably a good idea to keep all parts of the view window visible.
Step 7 - saving and closing your view
You can save your view in most of the usual Windows ways. For example:
Click on this tool, or press CTRL + S.
Alternatively, you could close it down, and Management Studio will ask if you want to save your changes:
Click on this cross to close down your view.
Confirm that yoiu want to save your view:
Click Yes to confirm that you want to save your view.
Give your view a name, and choose OK:
By convention, view names typically start with vw, to distinguish them from tables.
Your view name should appear in your list (occasionally you may have to right-click on Views and choose Refresh to update the list):
The new view in the list of views (you can expand the view to show what columns it includes).
Showing the rows returned by a view
You can right-click on any view to show what rows it returns:
The exact menu option depends on the version of SQL Server Management Studio that you're using.
You can now see the results of selecting all of the rows from your view:
The first 7 of the 111 rows returned by this view.
So that is how you create a view in design view - let's have a look now at the alternative: scripting a view.