View
A VIEW in SQL in completely based on the concept of Virtual Table which comes as a result of some SQL query. It behaves
like a real table and you can perform all SQL statements and function on a VIEW.
How
to create a VIEW:
Syntax:
CREATE VIEW [dbo].[View_Name]
as
select
A.Name, A.Email, A.Phone
from
tbl_name as A
where
A.status = 1
Example:
For
example let me create a table as tblEmployee
CREATE TABLE [dbo].[tblEmployee]
(
id int identity(1,1),
Emp_Code varchar(20) primary key,
Emp_Name varchar(500),
Emp_Email nvarchar(500),
Emp_Salary decimal,
Emp_Dept int,
Emp_Status int
)
Now I am going to create another table with the
name tblDepartment
CREATE TABLE [dbo].[tblDepartment]
(
id int identity(1,1),
Dept_Id int primary key,
Dept_Name varchar(500),
Dept_Status int
)
Now
based on these two tables I want to get details of those employees who are
currently working for the organization i.e. has Emp_Status as 1, for this I am
going to create a VIEW named as View_Emp_Active.
CREATE VIEW [dbo].[View_Emp_Active]
as
select
A.Emp_Code, A.Emp_Name, A.Emp_Email, A.Emp_Salary, B.Dept_Name
from
tblEmployee A inner join
tblDepartment B
on
A.Emp_Dept = B.Dept_Id
where
a.Emp_Status =
1
So, whenever you
need to get the list of active employees just call this View rather than
writing the full code of the select statement from two tables using join statement.
Some Important
things you should know about the SQL VIEW:
1. A
view can only be updated i.e. insert, delete, update statement can only applied
on the View if it is formed from s single table, otherwise you cannot perform
these actions on a View.
2. You
cannot have a view having more than 1000+ columns.
