How to create view in SQL Server

What is a View in SQL Server?

First let's understand what is a VIEW. A view is a virtual table or a logical table created on a single table or multiple tables using a query.

Syntax of View

Create VIEW view_name AS
SELECT column1, column2, ...
FROM dbo.table_name
WHERE condition

Benefits of a View

Views in SQL Server, are often created to segragete data from single or multiple tables into small chunks of virtual tables.

For example, let us assume, you have an Employee table that has employee details from different departments or regions. You can create a VIEW for each "department" or "region". In this way you have segragated (or separeted) the employee details into multiple virtual tables based on specific details.

The advantages are:

1) You do not have to query the entire "employee" table, rather query a particular view to access data of a particular department or region.

2) Users from x department will only have access to x data, not all the data. This will ensure security and data privacy.

3) Complex queries can be "encapsulated" in a view.

Now lets see how we can create Views in SQL Server. Like I said in the beginning, there are two ways.

Create View using a Statement

Views can created on a "single" or "multiple" tables. I'll create a view using a single table.

I have created some dummy tables here.

Let us assume, I have a table name birds. It has list of different types of birds.

You may also like... How to convert Table data into XML using a Query? Three simple methods.

I'll create a view each for each type of bird.

CREATE VIEW vDove AS
SELECT *FROM dbo.Birds
WHERE TypeOfBird = 'Dove'

To retrieve data from the view, you can write this query and execute.

SELECT *FROM dbo.vDove

Its as simple as quering a table. You can use a "WHERE" clause to filter data from the view. For example,

SELECT *FROM dbo.vDove WHERE ID BETWEEN 5 and 6

Create View using SSMS (SQL Server Managment Studio)

You can create or drop a view using SSMS. Just follow these steps.

1) Launch SQL Server Managment Studio

2) Expand the database you want to create the view. Right click "Views" and select "New View...". This will open "Add Table" window.

3) In the "Add Table" window, choose the table on which you want create the view. I'll select the Birds table. Click the "Add" button.

4) In the View window, you will see the table is selected. Now select all the columns one by one.

I'll create a view for each Bird type. So I need to filter out the type of bird.

Below, in the "Column" list choose TypeOfBird and the Filter column type the condition = 'Dove'.

If you have noticed, it also created the SQL query at the bottom tab, which now also has the WHERE clause.

5) Finally, in the top pane, right click and select Execute SQL. See the output at the bottom.

6) Press Ctrl + s to save the view. Name it vDove.

You will see that "vDove" view is created inside the Views folder.

Drop a View in SQL Server

Again, to drop or delete a View you can either do it using a Statement or through SQL Server Management Studio.

Drop view using Statement

DROP VIEW dbo.vDove

Drop view using SQL Server Management Studio (SSMS)

In the database, expand Views and right click the view you want to drop. Select the Delete option. This will open "Delete Object" window. Click the "OK" button at the bottom. That's it.

🙂

← Previous