Creating views in T-SQL or in the view designer
Part two of a five-part series of blogs

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.

  1. Views in SQL Server
  2. The View Designer (this blog)
  3. Scripting views using SQL
  4. The Pros of using Views in SQL Server
  5. The Disadvantages of Views

This is part of our full tutorial online on SQL.Have a look also at our SQL training courses for businesses.

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:

Creating 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:

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

These steps are shown in this diagram:

Adding tables to a view

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:

Two tables with relationship

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:

Ticking columns to be included

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:

Column names with aliases

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:

Sorting by two columns

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:

Filtering a view

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:

Executing a view

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:

Results window for view

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:

4 tools for parts of view window

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:

Save tool for a view

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:

Closing a view

Click on this cross to close down your view.


Confirm that yoiu want to save your view:

Confirm saving a view

Click Yes to confirm that you want to save your view.

Give your view a name, and choose OK:

Choose name for view

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):

New view in views 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:

Selecting top 1000 rows

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:

Rows returned by 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.

This blog has 0 threads Add post