SQL Server Create View

Learning Objective

The objective of this SQL Server tutorial is to teach you how to create a View on a table in a database.

What is a SQL Server VIEW?

A View is a virtual database entity which is created by selecting a set of columns from a table or tables. A view does not store data but the SELECT query using which it is created. The purpose of creating a view is to

  1. Hide the underlying complexity of the database and the multiple objects (including multiple tables) in it from the user.
  2. Preventing access to the underlying tables to the users thereby securing them.
  3. Making it easy for the user to retrieve data from a table or tables without the need to formulate and issue complex queries (including multiple joins and predicates) repeatedly since the view itself is the result of such a complex query.
  4. Backward compatibility with certain applications.


A view is created using the CREATE OR ALTER VIEW statement. It can be created by selecting columns from single or multiple tables using JOINS.

SQL Server Create View Syntax

The basic syntax of the CREATE VIEW statement is the following.

CREATE VIEW view_name
AS select_statement;

In this syntax,

  • CREATE VIEW – statement to tell SQL Server to construct a view from the query.
  • column_list – optional. List of view column names. If not specified it will take the underlying table(s) column names.
  • WITH – keyword to specify additional view features. It is optional and not required if you are not enabling any of the optional features described below.
  • ENCRYPTION – optional keyword to encrypt the query used to generate the view thereby protecting the view logic.
  • SCHEMABINDING – optional keyword to bind the view with the underlying schema. The effect it has is that it prevents any unauthorized or inadvertent changes to the underlying table(s) definition because doing so breaks the link between the view and the underlying table(s) making the view non-functional. Alteration is only possible after disabling schemabinding or dropping the view.
  • VIEW_METADATA – optional keyword to present the resultset generated by querying the view as coming from the view instead of from the underlying table(s) to client applications. This hides the underlying table(s) details from the client applications ensuring security.
  • AS – keyword to tell SQL Server that the view should be as the output of the SELECT query following AS.
  • select_statement – SQL query used to retrieve the resultset from the underlying table(s) to construct the view.

SQL Server Create View Example

Suppose we have a company employee database with an employees and departments table in it. The tables are represented below. We will use them as the sample tables and create two views – one using the employees table and another using both the employees and departments table to understand views practically.

101Human ResourceRebecca Rossi
106FinanceBruce Kent
Table: departments
1David JacksonMale8/8/199810127000
2Jim JamesonFemale11/26/199810127000
3Kate JohnsonFemale1/21/198710655000
4Will RayMale9/19/198910160000
5Shane MathewsFemale10/13/198710160000
6Shed PriceMale2/3/198710635000
7Viktor SmithMale2/22/200010630000
8Vincent SmithsonFemale9/15/199910635000
9Janice StreepFemale12/29/200010640000
10Laura WellsFemale1/1/200010150000
11Mac BullMale5/24/199610129000
12Patrick PattersonMale1/7/199910160000
13Julie OrbisonFemale4/24/198810660000
14Elice HemingwayFemale7/27/198810665000
15Wayne JohnsonMale3/8/199710665000
Table: employees

1) Creating Single Table View

Using T-SQL

As mentioned above first we will create a view using the employees table i.e. single table view. The following query does the same and creates a view by the name ‘new_view’ using the average aggregate function on employee salaries to filter the records of employees whose salaries are more than the average employee salary of 46533.33. Please note that in the query I have explicitly specified custom column names (id, name, sex, salary) for the columns of the newly created view.

CREATE VIEW new_view
(id, name, sex, salary)
SELECT emp_id, emp_name, emp_sex, emp_salary
FROM employees
WHERE emp_salary > (SELECT avg(emp_salary) FROM employees);

Using SQL Server Management Studio (SSMS)

The same can also be done from the SSMS GUI by following the below steps.

  1. In Object Explorer right click the View node and select New View.
SQL Server Create View

2. In the Add Table window that pops up select the employee table and click Add.

3. Once added the table columns will show up in a small pop up window for column selection. Select the check boxes against the required columns. It will also show the SELECT query in the bottom pane as can be seen in screenshot below.

4. Once done click on the Save icon (highlighted in yellow and red below) at the top in the menu bar and the view will be created.

Checking the View

Using T-SQL

We can check the creation of the new view by running a SELECT query on the view as below.

SELECT * FROM new_view;

The query succeeds and shows the following output.

3Kate JohnsonFemale55000
4Will RayMale60000
5Shane MathewsFemale60000
10Laura WellsFemale50000
12Patrick PattersonMale60000
13Julie OrbisonFemale60000
14Elice HemingwayFemale65000
15Wayne JohnsonMale65000

Using SSMS

The same can also be checked from SSMS by expanding the Views node. The view will show under the node as shown in the screenshot below. If you expand the view and the Columns node under it you can see the column list for the view.

sql server check view

2) Creating a View from multiple tables

In this example we create a view using columns from both the employees and departments table i.e. multiple tables. The following query does the same and creates a view by the name ‘ladies_in_departments’. The query uses an inner join between the two tables to get the department name of the employees from the departments table and then filters out the female employee records using a WHERE clause. Please note that in this example I have not explicitly specified custom column names for the view columns.

CREATE VIEW ladies_in_departments
e.emp_id, e.emp_name, e.emp_salary, d.dept_name as emp_department
FROM employees e
INNER JOIN departments d
ON e.emp_dept=d.dept_id
WHERE e.emp_sex='Female';

The same view can also be created using SSMS as detailed above. Only difference will be that you will have to select the appropriate columns from both the tables one after another.

Checking the View

Using T-SQL

We can check the creation of the new view by running a SELECT query on the view as below.

SELECT * FROM ladies_in_departments order by emp_department;

The query succeeds and shows the following output.

3Kate Johnson55000Finance
8Vincent Smithson35000Finance
9Janice Streep40000Finance
13Julie Orbison60000Finance
14Elice Hemingway65000Finance
2Jim Jameson27000Human Resource
5Shane Mathews60000Human Resource
10Laura Wells50000Human Resource

Using SSMS

The same can also be checked from SSMS by expanding the Views node as already explained and shown above. The view will show under the Views node with the columns in it as below.

sql server check view