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.

Operation

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
column_list
WITH ENCRYPTION | SCHEMABINDING | VIEW_METADATA
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.

dept_id	dept_name	dept_head
101	Human Resource	Rebecca Rossi
106	Finance	Bruce Kent

emp_id	emp_name	emp_sex	emp_dob	emp_dept	emp_salary
1	David Jackson	Male	8/8/1998	101	27000
2	Jim Jameson	Female	11/26/1998	101	27000
3	Kate Johnson	Female	1/21/1987	106	55000
4	Will Ray	Male	9/19/1989	101	60000
5	Shane Mathews	Female	10/13/1987	101	60000
6	Shed Price	Male	2/3/1987	106	35000
7	Viktor Smith	Male	2/22/2000	106	30000
8	Vincent Smithson	Female	9/15/1999	106	35000
9	Janice Streep	Female	12/29/2000	106	40000
10	Laura Wells	Female	1/1/2000	101	50000
11	Mac Bull	Male	5/24/1996	101	29000
12	Patrick Patterson	Male	1/7/1999	101	60000
13	Julie Orbison	Female	4/24/1988	106	60000
14	Elice Hemingway	Female	7/27/1988	106	65000
15	Wayne Johnson	Male	3/8/1997	106	65000

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)
AS
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.

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.

id	name	sex	salary
3	Kate Johnson	Female	55000
4	Will Ray	Male	60000
5	Shane Mathews	Female	60000
10	Laura Wells	Female	50000
12	Patrick Patterson	Male	60000
13	Julie Orbison	Female	60000
14	Elice Hemingway	Female	65000
15	Wayne Johnson	Male	65000

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.

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
AS SELECT
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.

emp_id	emp_name	emp_salary	emp_department
3	Kate Johnson	55000	Finance
8	Vincent Smithson	35000	Finance
9	Janice Streep	40000	Finance
13	Julie Orbison	60000	Finance
14	Elice Hemingway	65000	Finance
2	Jim Jameson	27000	Human Resource
5	Shane Mathews	60000	Human Resource
10	Laura Wells	50000	Human 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.