Print Friendly, PDF & Email

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

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.

  •  
  •  
  •  
  •  
  •  

LEAVE A REPLY

Please enter your comment!
Please enter your name here