What is a view? Disadvantages & Advantages of views with an example

Hello, welcome to Appsloveworld Technologies, In the session, we learn about what a view is and the advantages of using views.

what is a view?

We can consider the view as nothing more than a saved SQL query. We can consider a view as a virtual table .

Let’s look at an example that will make this definition clear. In the below image you can see the University’s table. We just got University_Id and University name columns and the second is the Professor table.which has got id, name, salary, gender, and University_Id columns.

Now I want you to write a query that can produce the output that you can see on the below image.

So the Id, name, salary, and gender comes from the TblProfessor’s table and the University name comes from the TblUniversity table.

So obviously to produce this output we will have to join the TblProfessor table with the TblUniversity table. And we have seen how to do that in SQL join series article. We have talked about Joins extensively in the below post.

• What is Sql Joins? With Realtime Examples

so here we have a join query which returns Id name salary, gender, and University name from these two tables by joining them.

Select Id, Name, Salary, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblProfessor.University_Id = TblUniversity.University_Id

so now how to create a view,for creating a view we use the “create view” statement. Just like  if we want to create a table we use “create table” and along the same lines, we want to create a procedure we use the “create procedure” statement.

Create View vWTblProfessorDetail
as

Select Id, Name, Salary, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblProfessor.University_Id = TblUniversity.University_Id

In the name of The View, we have prefixes that “VW” standing for view. so just by looking at the name of a SQL server object, you know, if you follow the right naming conventions, you can tell what type of object it is

For example, if it’s a table we prefix that usually with “tbl”, if it’s a stored procedure, we prefix that that “sp”, so along the same lines, if it’s a view prefix that that VW so just by looking at the name of an object, you can tell what type of object it is.

So “create view” view name “as” and then after “as” you specify you are select query.

Now let’s execute the above SQL query

So this view is now created in the SQL server. So once the view is created, how do you see that view?.

To see the view that is just created, go into the database, which is a TestDatabase  in my case, because we created this view in the TestDatabase  in our database. So expand TestDatabase   and look in the view folder

So when I expand that, and let’s refresh that. We can see the view vWTblProfessorDetail.

So now let’s say I want to select data from this view. How do I do that? You can treat this view just like a table.

So if I want to select data from that, I can just say SELECT * from vWTblProfessorDetail.

when you execute the select query, what actually happens is the database engine knows that this view is actually getting its data from the TblProfessor and people TblUniversity.

So for this view, Two tables at the underlying tables or base tables.  so when we execute the SELECT * from vWTblProfessorDetail, the SQL Server engine actually executes the select query on these two base tables and returns as the data from those tables.

so a view, in fact, doesn’t store any data. we are treating that view as a table. That’s why a view is called a virtual table. But in fact, when you look at the definition of the view, it doesn’t store any data. It is only a saved select query.

The reason why we say it as a saved select query is that when I say “sp_helptext” and then the name of The View, you can actually look at the definition of that view.

sp_helptext vWTblProfessorDetail

Let’s execute the above query see the definition of view
So as you can see in the above image, what is stored in the database? Only the select query. So a view is nothing more than a stored query. Or you can treat that as a virtual table.

Advantages of using views

Let’s turn our attention to what are the advantages of using views, where do we actually use these views.

• Now, views can be used to reduce the complexity of the database schema

Now, we have just done that. let’s say we have some non-technical users within our organization who interact with our database. So for a non-technical user join is a little complex topic.

so writing a Joins query is not easy for non-technical users, but they want to join maybe TblProfessor, TblUniversity, TblCountry, and all the other tables. Maybe they want to join like five to six tables

so in that case, what you can actually do is write a view which joins all those tables and then give access to that user to the view.

And then that user can treat that as a single table. And it is easy for him to query that table and they can simply use the where clause and filter whatever data they want. so obviously views can be used as a mechanism to simplify the database schema for non-technical users.

• Views can be used as a mechanism to implement row and column level security

Let’s look at an example of that. So how can views be used to implement row and column level security? if you look at the view that we have, this view is basically retaining all Professors within my institute.

Now, let us say there is a Michigan State University professor and I want to provide him access only to see Michigan State University professor.

In that case, if I grant him access to the table, what’s going to happen? He can issue a select query from those tables and he can see all the rows and all the columns.

So I don’t want to do that. I want to allow him access only to his University professor. So how can I achieve that?

In that case, create a view and within the view that return only Michigan State University professors and  give access to that view. Let’s do that.

Create View vWTblProfessorMichigan_State_University_Detail
as

Select Id, Name, Salary, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblProfessor.University_Id = TblUniversity.University_Id
where TblUniversity.UniversityName='Michigan State University'

SELECT * from vWTblProfessorMichigan_State_University_Detail

And then once we grant access to that professor, to this view, he can only see Michigan State University professors and nobody else.

We can also implement column-level security, for example, in any organization, salary is confidential information, and we don’t want everybody to see the salaries of other people.

In that case, we can actually create a view that hides the salary column and then retrieves all the other columns.

Create View vWTblProfessor_withoutsalary_Detail
as

Select Id, Name, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblProfessor.University_Id = TblUniversity.University_Id

So views can be used as a mechanism to implement row-level and column level security.

• Views can also be used to present only aggregated data

For example, let’s say you want to prevent the end-users from looking at the detailed data. You want to provide them only with aggregate data. So how can we do that again? Using views.

let’s say, for example, I want the total number of professors by University, for example, at Michigan State University.

How many professors are there? So we want the total number of professors by University to summarize data. And obviously, to do that, we have to use group by clause. And we have seen how to use the group by clause in below post.

• How to use group by in SQL Server query?

Create View vWTblProfessor_by_University
as

Select UniversityName, COUNT(Id) as TotalProfesssor
from TblProfessor
join TblUniversity
on TblProfessor.University_Id = TblUniversity.University_Id
Group By UniversityName

Disadvantages or Limitation of View

• You cannot pass parameters to view

Let’s understand what we mean by this with an example. I have a table called UniversityStudent, which has Id, name, Marks, gender, and University_Id columns.

Now, let’s create a view on this table. So here we are creating a view called vWGetStudentDetail, and this view is written to select all the columns from the universitystudent table.

Create View vWGetStudentDetail
as

Select Id, Name, Marks, Gender
from UniversityStudent

select * from vWGetStudentDetail

Now let’s try to pass a parameter to this view and see what actually happens.

If you look at the view implementation on below, the difference between these two views is that in the view here, we are actually passing an Id parameter of type int and we are using that parameter to filter the rows from table UniversityStudent

Create View vWGetStudentDetail
@Id int
as
Select Id, Name, Marks, Gender
from UniversityStudent where id=@Id

Now, is this possible in view? It’s not possible. You cannot pass parameters to views. what will happen when we try to do that? We will get an error.

So if you want to do that, you can actually use the where clause Just in case I want only Student with id=1, I can use the where clause where Id is equal to 1 and I only get a student with id=1.

select * from vWGetStudentDetail where id=1

You can also use a table-valued function as a replacement for parametrized views. In fact, we have spoken about that when we were discussing about user define -functions.

let us see how to create a function that can act as a replacement for a parametrized view.

Create function fnStundentDetails(@Id int)
Returns Table
as
Return
(Select Id, Name, Gender, Marks
from UniversityStudent where Id = @Id)

Select * from dbo.fnStundentDetails(1)

This is an inline table-valued function which is taking a parameter and the way we invoke the function, look at this, it’s exactly the same way as the Invoke a view.

select * from the function name and we pass in the parameters that it expects in this case Id.

so parametrized views cannot be created in SQL Server, but table-valued functions can be used as a replacement

• Rules and defaults cannot be associated with the view

So this is another limitation that we have. And it makes sense because views are virtual tables. They don’t really store any data except for the indexed views .That’s why it makes sense not to have rules and defaults being associated with the view.

• The order by clause is invalid in view unless top or FOR XML is also specified. So in a view definition, you cannot use the order by clause.

Let’s try to use an order by clause in view and see what actually happens.

Create View vWStudentDetail
as

Select Id, Name, Marks, Gender
from UniversityStudent order by id desc

when executing the above query, it will give us an error.

“Procedure vWStudentDetail, The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions unless TOP, OFFSET, or FOR XML is also specified.”

So you cannot use the order by clause unless you use the top or for XML keywords.

• You can not create a view on temporary tables

Let’s look at that in action so we have a temporary table called #TblUserDetail, and this is a simple temporary table that has got id, name columns.

And we are inserting some sample data into that temporary table. Again, we have spoken about local temporary tables and global temporary temporary tables in a great in below post.

So if you are new to temporary tables, I strongly recommend reading the below post first.

• Temporary Tables in sql server with real time example

Create Table #TblUserDetail(Id int,Name varchar(100))
insert into #TblUserDetail values(1,'john')
insert into #TblUserDetail values(2,'mike')
select *from #TblUserDetail

Now let’s create a view on that temporary table.

Create View vWTblUserDetail
as
Select Id, Name
from #TblUserDetail

Can you create a view on temporary tables? You cannot. If you try to do that, you will get an error message stating.

“Msg 4508, Level 16, State 1, Procedure vWTblUserDetail, Line 4 [Batch Start Line 3]
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.”

So those are the limitations on views,

1. You cannot pass parameters to a view.
2. Rules and defaults cannot be associated with the views.
3. The order by clause is invalid in views unless top or FOR XML, keywords are also specified.
4. Views cannot be based on temporary tables.

To modify a view we can use alter view statement

• ALTER VIEW statement

To Drop a view – DROP VIEW vWName

Sql Table Script

/****** Object: Table [dbo].[TblProfessor] Script Date: 09/30/2020 5:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProfessor](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[Salary] [float] NULL,
[University_Id] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblUniversity] Script Date: 09/30/2020 5:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUniversity](
[University_Id] [int] IDENTITY(1,1) NOT NULL,
[UniversityName] [nvarchar](max) NULL,
CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED
(
[University_Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblProfessor] ON
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (1, 'Christopher', 'Male', 50000, 2)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (2, 'Mackenzie', 'Female', 60000, 2)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (3, 'Julia', 'Female', 40000, 4)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (4, 'Connor', 'Male', 30000, 3)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (5, 'Madeline', 'Female', 90000, 6)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (6, 'Easton', 'Male', 35000, 5)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (7, 'Gianna', 'Female', 40000, 7)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (8, 'Chase', 'Male', 32000, 3)
GO
SET IDENTITY_INSERT [dbo].[TblProfessor] OFF
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] ON
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (1, 'Carnegie Mellon University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (2, 'Michigan State University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (3, 'Arizona State University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (4, 'The Australian National University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (5, 'The University of Arizona')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (6, 'The University of Melbourne')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (7, 'McGill University')
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] OFF
GO
ALTER TABLE [dbo].[TblProfessor] WITH CHECK ADD CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id])
REFERENCES [dbo].[TblUniversity] ([University_Id])
GO
ALTER TABLE [dbo].[TblProfessor] CHECK CONSTRAINT [FK_TblStudent_University]
GO

The post What is a view? Disadvantages & Advantages of views with an example appeared first on Software Development | Programming Tutorials.