What are Views in SQL and How to Use Them?

What are the Views in SQL

Views in SQL. This is my way of understanding concepts in the study. Whenever I want to understand a function or a concept or anything, I try to understand the need for that concept.

I would like to do the same thing here and I am sure you will understand the concept of views in SQL. Let’s take a scenario of when we need views.

Scenario

Suppose you are working in a company and you are the person who takes care of data. You are responsible for giving access to data to the teams. One day your boss comes to you and asks you to give read access of a table to a team, but he says the team needs the access of a few columns, not the entire table. The entire table has hundreds of columns which is irrelevant to this team. Figure out a way to do it as quickly as possible.

One solution to this problem is that it can be that you create another table and put the necessary columns in it and give access to this table to the team.

The drawback of this problem is that you will be responsible to manage two tables now.

Another drawback here is that the data is duplicated. The same data is saved at two different places that are the misuse of precious free space.

The best solution to this problem

The best solution to this problem can be the views. You can think of views in SQL as saved selected queries. We don’t have to write the select query time and again. You just need to create a view and in that view, you expose the selected columns in it. Let the team access this view.

One benefit of using views in SQL is that the team is accessing the data inside the table but they don’t have access to the entire table. This means data privacy policy is implemented nicely.

Another benefit of using views in SQL is that the views may have computed columns in it. You don’t need to make changes to the real database table for a particular team.

Suppose, the team who is getting the access, they have a special requirement. They want the first name and last name in a single column but in the real table, there is no such column where first name and last name are available in a single column. This means the team is asking you to make another column in the table and save the first and last name in it.

In big companies like Google, Microsoft, IBM, a lot of policies are in place. When you want to make changes into the structure of a table, these policies stop you. Making changes in the structure of a table is a huge deal and cannot be done for these types of small reasons.

To meet this special requirement, you need to create a computed column in the view, not in the table. You might not be aware of the concept of the computed column but do not worry.

In computed columns, you can join the data of two columns and combine them and save them in a single column. We will talk more about it in our future articles when we will discuss joins in SQL.

If you will make a computed column in the view that does not violate any policy because you are making a computed column in the view which is specially created for a particular team.

Enough talk, lets see how to create a view.

How to create a View in SQL

We will be using one table from the Adventureworks database named “Production.TransactionHistory“. So I will show you the entire table with the select query.

select * from Production.TransactionHistory

Create View Command Syntax

Create View Productwise_Transaction_History
as
select ProductID,TransactionDate,Quantity from Production.TransactionHistory

As you can see in the command above, a view named Productwise_Transaction_History has been created. The query saved inside this view will decide the output of this view. The query saved inside of this view is mentioned below.

select ProductID,TransactionDate,Quantity from Production.TransactionHistory

The output of query inside View

Please pay attention to the query that we have run to get the above-mentioned results. Now that the view has been created, we can get the same result by running a simple select query against the Productwise_Transaction_History. Let’s do it.

Select * from Productwise_Transaction_History

The output of View

Explanation

Now you have seen the output of both, the query inside view and the query against the view. Their output is exactly the same. Let’s look at their side by site so that we can see the difference.

select ProductID,TransactionDate,Quantity from Production.TransactionHistory

Select * from Productwise_Transaction_History

I hope you have understood the difference here. The above query is already big and can be even bigger. But the side of the select query will be the same always. That is why the views in SQL are also a lifesaver for both, the consumer of views and creator of views.

I hope the article was helpful. Your feedback is always appreciated. Feel free to give feedback in commend if any.

Take Care Guys!