Hey folks till now we have discussed User defined functions in sql, Kill Processes in sql server 2008, default constraint in sql , Remove Cross duplicate rows in SQL, Recursive SQL Query , Recursive SQL Query-2, STUFF and CONCAT in SQL, RANK in SQL server , Difference between temporary table and table variable in sql server, UNIQUEIDENTIFIER in SQL Server, RAW Mode with FOR XML , AUTO Mode with FOR XML, EXPLICIT Mode with FOR XML , PATH Mode with FOR XML, OUTPUT Clause in SQL Server, Difference between delete and truncate in sql server etc.

Today we will discuss regarding View. A view is a virtual table which contains columns from one or more table.  A view don’t have any data in it. A view is nothing more than the collection of columns from one or more table in the form of select query.  A complex view may contains the data from many tables.

Syntax : 

CREATE VIEW View_Name AS
SELECT column_name_1,column_name_2
FROM table WHERE condition

A view always shows up-to-date data. Each time user queries the view the data base engine recreates the data using the view’s SQL statement.

So lets go through the below example to create, update and drop view.

Suppose i have two tables of employee. One is employee which contains name,address and etc. The other table is employee details which contains salary, age ,department and other financial details.

CREATE TABLE [Employee](
	[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](20) NULL,
	[MiddleName] [varchar](20) NULL,
	[LastName] [varchar](20) NULL,
	[Address1] [varchar](50) NULL,
	[Address2] [varchar](50) NULL,
	[City] [varchar](20) NULL,
	[State] [varchar](20) NULL
)

CREATE TABLE EmployeeDetails
(
	[DetailId] [int] IDENTITY(1,1) NOT NULL,
	EmployeeId INT NOT NULL,
	Department VARCHAR(30),
	Age INT,
	Salary DECIMAL(18,3),
	PANNumber VARCHAR(30)
)

Now lets create a simple view with the use of above listed tables which don’t include the Salary and PANNumber from EmployeeDetails table.

CREATE VIEW EMPLOYEE_VIEW AS
SELECT E.FirstName,E.MiddleName,E.LastName,E.Address1,
E.Address2,E.City,E.State,ED.Department,ED.Age
from Employee E
Inner Join EmployeeDetails ED ON E.EmployeeId = ED.EmployeeId

So after creating above view the output of below query is as below.

SELECT * FROM EMPLOYEE_VIEW

 

Update View

We can  modify the definition of a SQL VIEW without dropping it by using the SQL ALTER Statement.

ALTER VIEW EMPLOYEE_VIEW AS
SELECT E.FirstName,E.MiddleName,E.LastName,E.Address1,E.Address2,E.City,E.State,ED.Department,ED.Age from Employee E
Inner Join EmployeeDetails ED ON E.EmployeeId = ED.EmployeeId
where State = 'Gujarat';

 

DROP View

We can drop the SQL view with the help of SQL DROP statement.

DROP VIEW EMPLOYEE_VIEW