--- title: "View in SQL Server" description: "A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a que" author: "AVADHESH PATEL" published: 2012-08-27 updated: 2017-11-29 canonical: https://www.mindstick.com/articles/983/view-in-sql-server category: "database" tags: ["database"] reading_time: 4 minutes --- # View in SQL Server A view is a [virtual table](https://www.mindstick.com/interview/876/which-virtual-table-does-a-trigger-use) that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are [referred to as](https://answers.mindstick.com/qa/97098/which-layers-are-referred-to-as-network-support-layers) base or underlying tables.\ Once you have defined a view, you can reference it like any other table in a database. ##### Use of a View Views are used for [security purposes](https://answers.mindstick.com/qa/39717/what-is-the-latest-technology-that-has-been-invented-or-is-going-to-be-invented-for-security-purposes) because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data. ##### Types of View These are two types of views. 1. Simple View 2. Complex View ##### Simple View A Simple View is a [user defined](https://www.mindstick.com/forum/34125/how-to-find-list-of-all-user-defined-tables-using-sql-server) view. A Simple View takes the data from a single table and has no function. The user defined view is created by the user as needed. We are showing a Simple View using a [student table](https://www.mindstick.com/forum/157561/what-is-the-stored-procedure-create-a-procedure-to-find-the-record-by-stu_id-from-the-student-table). ##### Syntax of a View ##### ``` CREATE VIEW  AS SELECT FROM WHERE ``` ##### Example of Simple View ``` --Create DataBaseCREATE DATABASE VIEWS --Create College TableCREATE TABLE COLLEGE(SNo INT IDENTITY(1,1),COLLEGE_CODE INT PRIMARY KEY,COLLEGE_NAME VARCHAR(50) NOT NULL,COLLEGE_ADD VARCHAR(50) NOT NULL) -- Create Simple ViewCREATE VIEW ALD_COLLEGEASSELECT * FROM COLLEGE WHERE COLLEGE_ADD='ALLAHABAD' ``` ##### Complex View A Complex View is created for [multiple tables](https://www.mindstick.com/forum/160909/how-to-join-multiple-tables-and-retrieve-specific-columns-in-sql-server) and contains functions and group data. ``` CREATE VIEW  AS SELECT FROM JOIN ON ``` ##### Example of Complex View ``` --Create DataBaseCREATE DATABASE VIEWS --Create Table1CREATE TABLE COLLEGE(SNo INT IDENTITY(1,1),COLLEGE_CODE INT PRIMARY KEY,COLLEGE_NAME VARCHAR(50) NOT NULL,COLLEGE_ADD VARCHAR(50) NOT NULL)-- Insert Values in COLLEGE Table -- Create Table2CREATE TABLE COURSE(SNo INT IDENTITY(1,1) PRIMARY KEY,COLLEGE_CODE INT FOREIGN KEY REFERENCES COLLEGE(COLLEGE_CODE),COURSE_CODE INT NOT NULL,COURSE_NAME VARCHAR(50) NOT NULL,COURSE_MAX_SEAT INT NOT NULL,COURSE_FEE MONEY NOT NULL)-- Insert Values in COURSE Table --CREATE VIEWCREATE VIEW DETAILSASSELECT COLLEGE.COLLEGE_CODE,COLLEGE.COLLEGE_NAME,COURSE.COURSE_FEE FROM COLLEGE  INNER JOIN COURSE  ON COLLEGE.COLLEGE_CODE = COURSE.COLLEGE_CODE ``` Drop View Simple and Complex View are Drop by same syntax ``` --Syntax for Drop Simple or Complex ViewDROP VIEW  --Example for Drop Simple or Comple ViewDROP VIEW DETAILS ``` Trigger on View ‘INSTEAD OF’ keyword used for creating Trigger on View. Basically there are three types of INSTEAD Of [Trigger in SQL](https://www.mindstick.com/articles/1440/trigger-in-sql) server which are namely define as follows: 1. INSTEAD OF INSERT Trigger 2. INSTEAD OF UPDATE Trigger 3. INSTEAD OF DELETE Trigger ##### INSTEAD OF INSERT Trigger: **INSTEAD OF INSERT** triggers can be defined on a **VIEW** or **TABLE** to replace the standard action of the [INSERT statement](https://www.mindstick.com/forum/157532/why-is-use-the-identity-column-in-the-insert-statement-in-sql). Usually, the INSTEAD OF INSERT trigger is defined on a view to [insert data](https://www.mindstick.com/forum/172/how-v-insert-data-into-table-and-display-into-gridview-in-aspx-cs-file-with-out-using-wizard) into one or more base tables. ##### Syntax: Creating Instead Of Insert Trigger ```  -----SYNTAX DEMONSTRATION OF INSTEAD OF INSERT TRIGGER--CREATE TRIGGER ON  OR INSTEAD OF INSERTASBEGIN----INSERT SQL QUERY TO INSERT DATA INTO TABLE----TRIGGER BODY SQL CODE---ENDGO ``` ##### Example: Creating Instead Of Insert Trigger ``` ----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF INSERT TRIGGER CREATE VIEW TEST_INSERT_INSTEADOF AS SELECT * FROM Userlogin ------DEMONSRATION OF CREATING INSTEAD OF INSERT TRIGGER--  CREATE TRIGGER TEST_INSTEADOF_TRIGGER  ON TEST_INSERT_INSTEADOF  INSTEAD OF INSERT  AS  INSERT INTO Userlogin  SELECT * FROM INSERTED  GO ``` ##### INSTEAD OF DELETE Trigger: INSTEAD OF DELETE triggers can be defined on a VIEW or TABLE to replace the standard action of the DELETE statement. Usually, the INSTEAD OF DELETE trigger is defined on a view to modify data in one or more base tables. DELETE statements do not specify modifications to existing data values. DELETE statements specify only the rows that are to be deleted. ##### Syntax: Creating Instead of Delete Trigger ```   -----SYNTAX DEMONSTRATION OF INSTEAD OF DELETE TRIGGER--CREATE TRIGGER ON  OR INSTEAD OF DELETEASBEGIN----DELETE SQL QUERY TO INSERT DATA INTO TABLE----TRIGGER BODY SQL CODE---ENDGO ``` ##### Example: Creating Instead of Delete Trigger ``` ----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF DELETE TRIGGER CREATE VIEW TEST_DELETE_INSTEADOF AS SELECT * FROM Userlogin ------DEMONSRATION OF CREATING INSTEAD OF DELETE TRIGGER--  CREATE TRIGGER TEST_INSTEADOF_TRIGGER  ON TEST_DELETE_INSTEADOF  INSTEAD OF DELETE  AS  INSERT INTO Userlogin  SELECT * FROM DELETEED  GO ``` ##### INSTEAD OF UPDATE Trigger: INSTEAD OF UPDATE triggers can be defined on a VIEW or TABLE to replace the standard action of the UPDATE statement. Usually, the INSTEAD OF UPDATE trigger is defined on a view to modify data in one or more base tables. UPDATE statements that reference views with INSTEAD OF UPDATE triggers must supply values for all non null view columns referenced in the SET clause. ##### Syntax: Creating Instead of Update Trigger ``` -----SYNTAX DEMONSTRATION OF INSTEAD OF UPDATE TRIGGER--CREATE TRIGGER ON  OR INSTEAD OF UPDATEASBEGIN----UPDATE SQL QUERY TO INSERT DATA INTO TABLE----TRIGGER BODY SQL CODE---ENDGO ``` ##### Example: Creating Instead of Update Trigger ``` ----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF UPDATE TRIGGER CREATE VIEW TEST_UPDATE_INSTEADOF AS SELECT * FROM Userlogin ------DEMONSRATION OF CREATING INSTEAD OF UPDATE TRIGGER--  CREATE TRIGGER TEST_INSTEADOF_TRIGGER  ON TEST_UPDATE_INSTEADOF  INSTEAD OF DELETE  AS  UPDATE Userlogin SET Id = deleted.id, Name = deleted.name   GO ``` --- Original Source: https://www.mindstick.com/articles/983/view-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.