SQL SERVER VIEW

SQL Server view is a virtual table based on the result set of SQL statement.

Views can be created from a single table or another view. It can also  Joins  and simplify multiple tables into a single virtual table.

Views take very less space to store , the database contains only the definition of a view and not a copy of all the data that it presents .

SYNTAX

CREATE VIEW view_name AS
SELECT column1, column2, ..... 
FROM table_name WHERE condition ..

Lets look at an example of SQL Views. First, we create a sample table named as EMPLOYEE, then insert some records into table.

CREATE TABLE EMPLOYEE (EMPID INT NOT NULL, 
EMPNAME VARCHAR(50), 
EMPCODE VARCHAR(15), 
DOB DATE)

INSERT INTO EMPLOYEE(EMPID, EMPNAME, EMPCODE, DOB)
VALUES(
501, 'RAJESH MITAL', 'XCV567', '1976-11-11'),
(502, 'SUJOY SINGH', 'XCB787', '1970-10-19'),
(503, 'ABHISHEK MISHRA', 'FJV542', '1976-09-19'),
(504, 'HARSHIT KAPOOR', 'NMD557', '1980-12-10'),
(505, 'MANISH RAWAT', 'QJB987', '1985-02-15'),
(506, 'DEEPAK KUMAR', 'FZV704', '1986-11-05'),
(507, 'HARSH KUMAWAT', 'NET657', '1986-07-01'),
(508, 'ROZER JR', 'DDB987', '1987-08-02'),
(509, 'BALAJI NEGI', 'KL0542', '1980-09-03'),
(510, 'NARAYANA SWAMI', 'NSD447', '1980-10-10'),
(511, 'LOKESH MANTRI', 'BBB007', '1990-05-19'),
(512, 'SUMIT VIYAS', 'FFNM04', '1990-06-29'),
(513, 'BABA NAYAK', 'NEG157', '1989-07-13')

Following SQL statement creates a view named as View_Employee which returns EmpID, EmpName, EmpCode and DOB from Employee table.

CREATE VIEW view_employee AS
SELECT EMPID, EMPNAME ,EMPCODE, DOB 
FROM EMPLOYEE

To fetch the data from view, you can query view_employee in a similar way as you query a regular table in SQL as shown below.

SELECT * FROM view_employee

Modifying the view 

Lets modify the above view and add a condition to fetch only those employees having Id > 505.

To modifying an existing view, you need to use alter command to modify the view.

ALTER VIEW view_employee AS
SELECT EMPID, EMPNAME ,EMPCODE, DOB
FROM EMPLOYEE WHERE EMPID >505

Now, execute the view and you can see the result. It returns only those employees having id > 505.

SELECT * FROM view_employee

Using conditions in Views

As above we have already seen, how to create a view with adding a condition on View. But if you do not want to specify the condition while creating a view then you can also use any logical and comparison condition on view during select the data from view using WHERE CLAUSE as it is similar like a regular table.

To fetch Employee details from view view_employee where employee name start with letter ‘B’

Remember we have already specified a condition in view to return only those employee’s details whose empid is grater than 505.

Now, we are going to filter view result for those employee whose name start with letter ‘B’.

 SELECT * FROM view_employee WHERE EMPNAME LIKE 'B%'

Updating rows with view

A view can be updated under certain conditions which are given below

1. View statement that have multiple tables can not be updated, only single table view can be updated.
2. View statement may not have order by keyword.
3. View statement may not have any subqueries .
4. View statement may not have distinct keyword.
5. View statement may not have  GROUP BY or HAVING clause.

Remember, View is a virtual table that is created from real table so, if you update any records in view it gets also updated in real table.

Lets look at an example of updating a records through a View .

First, verify the table and view records for EMPID = 508.

SELECT * FROM EMPLOYEE WHERE EMPID =508
SELECT * FROM view_employee WHERE EMPID =508

As you can see, above result set for employee table and View which returns the same data for EMPID =508.

Now, we update the EMPNAME value from ‘ROZER JR ‘ to ‘ROZER M. JR’ for this EMPID in view.

Following SQL statement updates the records in view , which will also update the this record’s value in real table as well.

UPDATE view_employee SET EMPNAME = 'ROZER M. JR' WHERE EMPID =508

Lets check the both view and employee table for EMPID = 508,  to verify whether the records is updated or not in real table. 

SELECT * FROM EMPLOYEE WHERE EMPID =508
SELECT * FROM view_employee WHERE EMPID =508

 

As you can see above result set, updating EMPName value for EMPID =508 in a view.

It gets updated too in real table.

Deleting rows with view

Rows can also be deleted from a view but it must follows all the rules that we have already seen in case of update .

Lets look at an example of delete statement with View. Now, lets delete the record for EMPID = 508 from view.

Following SQL statement deletes the row for EMPID =508 from view, that will also delete the record from real employee table as well.

 DELETE FROM view_employee WHERE EMPID =508

Lets fetch the record for EMPID =508 from view and employee table to verify whether the record is deleted from real table or not .

SELECT * FROM EMPLOYEE WHERE EMPID =508
SELECT * FROM view_employee WHERE EMPID =508

 

As you can see, it returns empty data which means record for EMPID =508 is deleted from both view as well as from  Employee table.

Inserting a rows with view

Rows can also be inserted from a view but again it must follows all the rules as given for update and delete .

Lets look at an example of insert statement with View.

Following SQL statement inserts a record in view.

INSERT INTO view_employee (EMPID, EMPNAME, EMPCODE, DOB)
VALUES (514, 'RHOIT KUMAR','FYX704', '1986-12-25')

Lets verify this records in employee table and view .

SELECT * FROM EMPLOYEE  
SELECT * FROM view_employee

 

As you can see, new record is inserted in view as well as in employee table.

Drop a view

Following SQL statement drops the view only, it does not drop the real table from database.

DROP VIEW view_employee

Lets select records from view, to verify is it still exits or dropped from database.

SELECT * FROM view_employee

 

As you can see, it gives an error of an invalid object name ‘view_employee’.

It means view has been dropped from database.

Also Read..

SQL Server Output Clause

SQL Server Merge

SQL Server Pivot

SQL Server Dynamic Pivot

SQL Server Unpivot