A view is just an SQL statement and more than a SQL statement that is completely stored in database storage with a given associate name. Furthermore, Views also contain rows and columns just as in real tables. Therefore, A View is treated as a Virtual table.
So, Let’s figure it out as a real example. Opposite, we create two tables as the name of tblEmployee and tblDepartment to use views.
The syntax of creating table tblEmployee:
CREATE TABLE tblEmployee
(
Id int Primary Key,
Name nvarchar(30),
Salary Int,
Gender nvarchar(30),
DepartmentId int
)
The syntax of creating table tbDepartment:
CREATE TABLE tblDepartment:
(
DepartId int Primary key,
Deptname nvarchar(20),
)
Now let’s insert data records into tblDepartment table
Jump to Section
Insertion 1
Insert into tblDepartment values (1,’IT’)
Insertion 2
Insert into tblDepartment values (2,’Payroll’)
Insertion 3
Insert into tblDepartment values (3,’HR’)
Insertion 4
Insert into tblDepartment values (4,’Admin’)
So, let’s insert data records into tblEmployee table
Insertion 1
Insert into tblEmployee values (1,’John’, 5000, ‘Male’, 3),
Insertion 2
Insert into tblEmployee values (2,’Mikes’, 3500, ‘Male’, 2),
Insertion 3
Insert into tblEmployee values (3,’Pam’, 6000, ‘Female’, 1),
Insertion 4
Insert into tblEmployee values (4,’Todd’, 4800, ‘Male’, 4),
Insertion 5
Insert into tblEmployee values (5,’Sara’, 3200, ‘Female’, 1),
Insertion 6
Insert into tblEmployee values (6,’Ben’, 4800, ‘Male’, 3)
Finally, the output for both the tables will look like the tables given below;
Table: tblEmployee
Table: tblDepartment
So, the question is, I want the only output as shown below;
To get the above output, we need to use join in tblEmployee table with tblDepartment table.
Select Id, Name, Salary, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
So, let’s create a single view, by using the JOINS query statement, we have just written above.
Create View vWEmployeesByDepartment
as
Select Id, Name, Salary, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
To select data from the view state, and the only SELECT statement can be used, we use it with a table to run a command.
SELECT * from vWEmployeesByDepartment
When the above query executes, the database engine automatically retrieves the data from both tables tblEmployee and tblDepartment. As a result of the View itself, it does not store any data itself by default. In addition, we must change this default behavior of view. So, this is the only reason, a view is considered as a virtual table.
Advantages of using views
Database views can be used to reduce the database complexity, for only non-IT users. The sample view and vWEmployeesByDepartment, this view hides the records complexity of joins. For non-IT end users, therefore, It’s easy to query the view in place of writing complex joins query.
Furthermore, Database views provide a high-security level to implement row level and column level security.
Row Level Security
So, let’s understand row-level security with an example, and I want an end user for a moment, that end user only has access to view records of IT Department employees, and If I grant him access to the defined tables tblEmployees and tblDepartments, he will be able to look on every department employees. To get this schema, as a result, we can create a view. In conclusion, which returns only IT Department employees records in output.
A view that returns only IT department employees:
Create View vWITDepartment_Employees
as
Select Id, Name, Salary, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
where tblDepartment.DeptName = ‘IT‘
Finally, the output will look like as given below;
Column Level Security
So, let’s understand column level security with an example, and Salary is much confidential information anywhere in the corporate world and I want to prevent access to that column only. To get this schema, as a result, we can create a view. In conclusion, which returns only the Salary column, then grant access to the end user access to this views properly rather than the base underlying tables.
A view that returns all columns except Salary column:
Create View vWEmployeesNonConfidentialData
as
Select Id, Name, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
Finally, the output will look like as given below;
Database views can be used to represent only aggregated data and hide summarized detailed data. Therefore,
A database view that returns only summarized data, Total number of employees by Department.
Create View vWEmployeesCountByDepartment
as
Select DeptName, COUNT(Id) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
Group By DeptName
If need any modification in the statement just type
ALTER View statement
If you want to drop the view statement just type
DROP view vWName
How to Update Views
Most of the people do not know about an update in views. Yes, we can update with any type of views. Opposite, when you update in view table it also updates your base table.
So, Let’s take an example, we have a table as shown below;
So, I want to update in column name Arun to Aruna,
Example:
CREATE VIEW VW_UpdateView
AS
SELECT NAME, SALARY, GENDER, Dept_Name FROM CUSTOMER INNER JOIN Department
ON Customer.Department_ID= Department.ID
UPDATE VW_COLUMN_SECURITY set NAME=’ARUNA’ where Name=’Arun’ and Dept_Name= ‘IT’
SELECT * FROM VW_UpdateView
Finally, the output will look like as shown below;
Limitations of Views
Parameters can be passed by views, and only with the help of Table Value Functions can be used by parameterized views.
So, let’s Suppose if we create view like this as;
Create View vWEmployeeDetails
@Gender nvarchar(20)
as
Select Id, Name, Gender, DepartmentId
from tblEmployee
where Gender = @Gender
Therefore, It will throw an error because it cannot parameters to view. There is a correct format for writing a final query.
Create function fnEmployeeDetails(@Gender nvarchar(20))
Returns Table
as
Return
(Select Id, Name, Gender, DepartmentId
from tblEmployee where Gender = @Gender)
Finally, to get an output, call this function
Select * from dbo.fnEmployeeDetails(‘Male‘)
Rules and Defaults in views cannot be associated with tables records.
Invalid ORDER BY clause in views
When you create a view with order by you will get an error like;
A temporary table does not exist in views and a temporary table is just a separate database in SQL server. Opposite, it has own database name by default as ‘tempdb’ folder. So, let’s quickly create a temporary table.
Create Table ##testTemptable(Id int, Name nvarchar(20), Gender nvarchar(10))
Insert into ##TestTempTable values(101, ‘Martin’, ‘Male’),
Insert into ##TestTempTable values(102, ‘Joe’, ‘Female’),
So, create a view with temporary table
Create View vwOnTempTable
as
Select Id, Name, Gender
from ##TestTempTable
When you executed this command, execution will throw an error: Cannot create a view on Temp Tables.
Conclusion
A view is nothing more than a saved SQL statement which is mainly used to reduce the complexity of the database server. Therefore, A view is essentially a query definition which does not contain any data records. So, It is the composition of the table in the form of pre-defined SQL query. It also retrieves data from one table to another table. A view is nothing but a physical copy of data and does not contain any data itself. Views are typically very useful for speeding up the development process while taking long running time, it can also kill database performance completely. So, View is the small entity which saves running time.
I am a database administrator who has the responsibility of maintaining the integrity and performance of company databases. I guarantee that the data is stored securely and ensure all the users are informed of any changes in the same. Apart from managing all this bulk of technological binaries, I love to enjoy little things in my life. I am an avid foodie who loves to explore the abundance of specialties our cultures offer. I am also into sports, especially cricket. I make sure that I never miss my weekend morning cricket matches alongside my long-time friends. I cherish both my professional and personal lives to the fullest.
