How to create a View in SQL Server
Tags: beginners, database, sql
Published: 2022-05-17 11:39:38

Today I am going to explain how to create a view in SQL Server. Let’s first understand what SQL Server view is. The meaning of View in English is the ability to see something or to inspect something. Now, we will correlate this in SQL Server. 

We know _Tables_ in SQL Server, Tables are used to store data in the forms of rows and columns in relational database systems like SQL Server, Oracle, or other RDBMS systems. SQL Server VIEW is a database object which offers us the ability to see or inspect what is there in their respective Tables on which VIEW has been created without touching or accessing the actual Table in which data is stored. 

Views do not store data physically in the database and are fetched directly from their respective tables whenever you try to access that View and that’s why we term it as a virtual or logical table.

When you access a View, it executes its SQL query and returns the output as per the data selection in the query. You should also note that when you create an index on the View, data got physically stored in the database for that View.

Next, you might be thinking about what the use of SQL Server VIEW is if we already have Tables in the database. The reason for creating a view in SQL Server is to address below concerns of the database users.

- Users can look at or customize data from different perceptions based on their needs. They don’t need to make any changes in actual tables, rather they can get filtered columns from multiple tables in a single VIEW.
- VIEWs are useful if you don’t want to allow users to access the database tables. You can create a VIEW based on the user's need and ask them to use that View as a table for their business need. It also improves security and secure table data.
- VIEWs are also useful in a backward compatible interface to a table that used to exist, but its schema has changed over time.
 
There are several types of VIEWs in SQL Server. 

- User-defined view
- System view
- Indexed view
- Partitioned view

_User-defined View_ is a standard view that a user can create either using query or using GUI through SQL Server Management studio.

_System View_ is an inbuilt system created catalog view using which we fetch various details of SQL Server instances like sys.databases, sys.database_files, etc. 

Next is _Indexed View_, if you create an index on a view then it means the view definition will be computed, and the resulting data will be stored in the database, unlike standard view. Indexed views are created by creating a clustered index on a view. It also improves database performance and is best suited for queries that aggregate more rows. 

The last one is _Partitioned View_ which is used to join horizontally partitioned data from a set of member tables across one or more servers.

Now, we will learn how to create a VIEW in SQL Server. We can create a view using T-SQL as well as using GUI through SQL Server Management Studio. Let’s start with the T-SQL statement.

```
-- Create View Syntax
CREATE VIEW ViewName AS
SELECT ColumnName1, ColumnName2, ColumnName3,..
FROM TableName
WHERE condition;
```

I have a test database named **TESTDB** as shown in the below image. You can see the list of all views in the Views folder of the database. You need to connect to your SQL Server instance and then expand the **Databases** folder. Expand your target database in which you want to create a view or you want to see a list of views of a database. 
I have expanded the **Views** folder of database **TESTDB** and we can see there is no user-defined view in this database. You can get a list of all system views by expanding the System Views folder shown under the Views folder of the database **TESTDB**. 

![See SQL Server Views in SSMS](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/azfhnaudx8coao52460h.png)
 
 

## Create SQL Server View using T-SQL

We will use **_CREATE View_** T-SQL statement to create a view in SQL Server. The syntax of view creation is already given in the above section. 

Now, I want to create a view named ProductSalesVW in the **TESTDB** database where I will include 3 columns _PersonName_, _Price_, and _InvoiceMonth_ from table **OrderDetails** to display the list of product sales each month.

```
-- Create a View to display month wise product sales
CREATE VIEW ProductSalesVW AS
SELECT ProductName, Price, InvoiceMonth
FROM OrderDetails 
```
We can execute the above T-SQL statement to create the view. The above statement was successfully executed as shown in the below image.

![Create View using T-SQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/blu7xmhkkme5vrtdtmc9.png)
 
 
You can also verify whether a view has been created in your database or not by expanding the Views folder of the database **TESTDB** as shown in the below image. You can use system object **_sys.views_** to get a similar list. I will show you later how to use this system object to get the list of all views.
 
![Verify newly created View](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ogdhkl3yyqjc9k2yv3ul.png)
 

Now, you can fetch data from the newly created View. You just need to simply run the _SELECT_ statement the way you run on any table to fetch its data. 

Here, I have executed a simple **_SELECT_** statement to return all data from this view and we can only see 3 columns that we have chosen to create this view.

 ![View validation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g5qgjhn4iiprjotge4le.png)
 

If you want to apply specific conditions to filter the output then you can also do it by using the **_WHERE_** clause in the **_CREATE VIEW_** statement. 

Next, let’s create a new view named **HighValueProductSalesVW** which will display a list of all products for which invoice cost is more than $1000.

```
-- Create View to display month wise product sales where the invoice is more than $1000
CREATE VIEW HighValueProductSalesVW AS
SELECT ProductName, Price, InvoiceMonth
FROM OrderDetails 
WHERE Price >=1000
```

Once you will execute the above statements, a new view will be created. Let’s fetch the data using this newly created view. The below image is showing the output of the newly created view and we can see only products whose price is more than $1000.

 ![Validate the newly created View](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nkjpghjcsdmpxo46pps1.png)
 

Next, let’s create a view named **EmpProductSalesVW** using which we will fetch data from more than one table. We will take an extension of the above example where we will add additional table **Associates** to fetch employees’ details who have sold these high-value products.
 

```
-- Create View to display month wise product sales where the invoice is more than $1000
CREATE VIEW EmpProductSalesVW AS
SELECT o.ProductName, o.Price, o.InvoiceMonth, a.Name, a.Designation
FROM OrderDetails o INNER JOIN Associates a
ON o.PersonID=a.ID
WHERE Price >=1000
```
The above view has been successfully created in the below image.

 ![View creation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mbgu00q2suw0ymfzcgyk.png)

 
Next, we will run this newly created view to fetch its output and we can see employee names and their designation is showing in its output.

 ![Validate the newly created View](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fxpqni9vmg7edui2rsjw.png)
 

Till now, we learned how to create SQL Server views with different requirements in the above steps. Next, we will learn how to validate or check these newly created views using the T-SQL statement. If you want to display a list of all user views then you can simply run the below T-SQL statement to get this list. I have executed the below statement and got the list of views that we have just created in this article. I don’t had any views created in this database earlier.

```
-- Get a list of all Views
SELECT name, type, type_desc, create_date
FROM sys.views
```

Have a look at the output of the above query. It is showing all 3 views which we created in the above steps.

 ![Check all Views using sys.views](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0w8hpitmzuemgmo4dr65.png)
 

You can also use another system view **_sys.all_views_** to display the list of all views created for your target database including all system views as well.

## Create SQL Server View using GUI through SQL Server Management Studio

This section will demonstrate step by step process to create a SQL Server View using GUI through SQL Server management studio. There are various tools or add-ins also available in market for database development like _dbforge_ [SQL autocomplete](https://www.devart.com/dbforge/sql/sqlcomplete/) which you can use for this activity.

First, you need to connect to your SQL Server instance in SQL Server management studio on which your target database is hosted under which you will create a view. Expand the _Databases_ folder in the management studio and finally target the database in which we will create the View. I will create another view in my test database TESTDB.
Right click at the _Views_ folder and click on the **_New View…_** option as shown in the below image.
 
![Create View using GUI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z6p1lqvesry0a2vsmtid.png)

 
You will get the below window to add tables from which you want to select columns that will be included in the view. I have selected 2 tables as highlighted in the below image and then clicked on the **Add** button. I will get the product name and POC (point of contact) from one table _OrderDetails_ and details of each POC from another table named _Associates_.

 ![Table selection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3d34byufoozdz1v2tehg.png)
 

Once you will click on the **Add** button post selecting all your desired tables from which columns will be selected in the View, another popup window will be displayed for each table containing their column names as shown in the below image. This section is known as the **_Diagram pane_**. Now click on the **Close** button from _Add Table_ window to close that window.


 ![Choose columns](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g4ksjgtdvgdp6qszodyi.png)
 

The relation between tables will be displayed as per their relationships or you can also join both tables based on their relation key. You can see the link between both tables on column **_Associates.ID_** and **_OrderDetails.PersonID_**. 

The next step is to select columns that will be part of View. Click at the check box to select or uncheck the check box to deselect the columns. You can also see the T-SQL statement based on your selection in the query pane.

The next option is to choose criteria for your view and that is why we call it the Criteria pane. Here, you can choose sort type, sort order, filter output based on a specific condition, or add an alias for your column names. You will notice a T-SQL statement showing just below the criteria pane will keep on changing based on your selection or deselection criteria.


![View design](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/29bz8i3dac97xhtxlfzm.png)
 
 

Once you have designed your View then you can execute it to display the result in the same window. Right click anywhere on the screen and click on the **_Execute SQL_** option as given in the below image.

![Execute SQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5sghwxf3rzjx6dowvjeo.png)
  

You will get its output in the output section as shown in the below image. Here you can understand the data whether it is adequate and as per your need or not. You can make changes based on your needs to change the data perspective. We can see all data returned from this View in the below screen and it is absolute as per our expectation.

 ![Execute SQL for View](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pcwf32184wwsducub1vk.png)
 

Next, we will save this View. Click on the **_Save_** button, you will get the below screen to name this view. Enter the appropriate name under which you want to save this view.


 ![Name the View](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3398gl5w4we1tsicwegm.png)
 

You can validate it by either expanding the **Views** folder or by accessing the **_sys.views_** system object to ensure this view has been created or not. New View is shown in the below image during our validation.

![Validate newly created View](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4i3eog8snjp2kyktpi15.png)
 
 
If you want to access the data from this newly created view, you can run it SELECT statement as I have executed in the below image to display its data.

![Validate View](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4rznbs54pf1uaw10ycoc.png)
 
 

## Conclusion
Today, I have explained the basic understanding of SQL Server View and its step-by-step process to create it, check it or access it to get its data. Please share this article and comment with us your feedback in the comment section so that we can improve in a better way.