What is a View? Disadvantages & Advantages of Views With an Example

In the post, we learn about what a view is and the advantages of using views.

What is a view?

We can think of a view as a saved SQL query, essentially creating a virtual table. Let’s clarify this definition with an example. In the image below, you can see two tables: University and Professor.

The University table contains columns for University_ID and University name, while the Professor table contains columns for ID, name, salary, gender, and University_ID.

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 come from the TblProfessor table, while the University name comes from the TblUniversity table.

To generate this output, we need to join the TblProfessor table with the TblUniversity table. We have discussed joins extensively in the SQL Join series article, which you can find below.

• What is Sql Joins? With Realtime Examples

So here we have a join query that returns the 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

How do we create a view? 

For creating a view, we use the `CREATE VIEW` statement. Just like when we want to create a table, we use `CREATE TABLE`, and along the same lines, when 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 the prefix 'VW' standing for view. So just by looking at the name of a SQL Server object, 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 it usually with 'tbl'. If it's a stored procedure, we prefix it with 'sp'. So along the same lines, if it's a view, we prefix it with 'VW'. So just by looking at the name of an object, you can tell what type of object it is.

So it's 'CREATE VIEW', followed by the view name, then 'AS', and after 'AS', you specify your select query.

Execute the above SQL query

So, once the view is created, how do you view it?

To see the view that was just created, navigate to the database where the view was created. In my case, it's the TestDatabase. Expand the TestDatabase and look in the 'Views' folder.

When you expand that folder and refresh it, you will see the view named 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 it, I can simply use the query: 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 TblUniversity 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 the data from those tables.

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 is 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

As you can see in the image above, what is stored in the database? Only the select query. Therefore, a view is essentially just a stored query, or it can be regarded as a virtual table.

Advantages of using views

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

Let's examine an example of this. How can views be utilized to enforce row and column level security? If we inspect the view we have, it essentially filters out all professors within my institute.

Now, let's consider a scenario where there is a Michigan State University professor, and I only want to grant them access to view Michigan State University professors.

In this case, if I grant them direct access to the table, they would be able to issue a SELECT query and see all rows and columns, which is not what I desire. Instead, I want to restrict their access to only their university's professors.

To achieve this, I will create a view that returns only Michigan State University professors, and then grant access to that view. Let's proceed with that approach.

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 once access is granted to this view, the professor will only have visibility into Michigan State University professors and no others.

Furthermore, we can implement column-level security measures. For instance, in many organizations, salary information is considered confidential, and it's important to restrict access to it. 

In such cases, we can create a view that excludes the salary column, thereby concealing it while still allowing access to 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 instance, if you aim to restrict end-users from accessing detailed data and only provide them with aggregated data, views can again serve this purpose.

Let's say, for instance, you want to retrieve the total number of professors by university, such as at Michigan State University. This involves summarizing the data by university, which typically requires the use of a `GROUP BY` clause. We've discussed how to utilize the `GROUP BY` clause in a previous 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 delve into an example to illustrate this concept. Consider a table named `UniversityStudent`, comprising columns such as `Id`, `Name`, `Marks`, `Gender`, and `University_Id`. This table likely stores information about students enrolled in various universities.

Now, let's create a view based on this table. We'll name the view vWGetStudentDetail, and it's designed 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 attempt to pass a parameter to this view and observe its behavior.

In the view implementation below, the distinction lies in how we're passing an 'Id' parameter of type int and utilizing it to filter the rows from the UniversityStudent table.

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

Is it possible to pass parameters to views? No, it's not. Views do not support parameter passing. Attempting to do so will result in an error.

If you want to achieve that, you can simply utilize the WHERE clause. For instance, if I only want the student with Id=1, I can specify 'WHERE Id = 1' and retrieve only the student with that specific Id.

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 that accepts a parameter. Notice how we invoke the function in exactly the same manner as invoking a view.

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

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

This limitation is understandable because views essentially represent virtual tables that don't store data themselves, except for indexed views. Therefore, it makes sense not to allow rules and defaults to be associated with views.

Another restriction is that the ORDER BY clause is invalid in views unless the TOP or FOR XML clauses are also specified. In a view definition, you cannot directly use the ORDER BY clause.

Let's attempt to include an ORDER BY clause in a view and observe the outcome.

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 see this in action. We have a temporary table named #TblUserDetail, which is a simple table with columns for id and name.

We're inserting some sample data into this temporary table. If you're unfamiliar with temporary tables, I recommend reading the post below, where we discussed local and global temporary tables extensively.

• 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.”

Those are indeed the limitations on views:

• You cannot pass parameters to a view.
• Rules and defaults cannot be associated with views.
• The ORDER BY clause is invalid in views unless the TOP or FOR XML keywords are also specified.
• 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