Different Types of views in sql server
Views are virtual tables that are compiled at runtime. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. A view can be made over one or more database tables. Generally, we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query view like a table. We can make an index, trigger on the view.
In SQL Server we make views for security purpose since it restricts the user to view some columns/fields of the table(s). Views show only those columns that are present in the query which is used to make a view. One more advantage of Views is, data abstraction since the end user is not aware of all the data present in the database table.
Syntax for Creating View
CREATE VIEW view_name AS select_statement
Types of Views
In SQL Server we have two types of views.
System Defined Views
System-defined Views are predefined Views that already exist in the Master database of SQL Server. These are also used as template Views for all newly created databases. These system Views will be automatically attached to any user-defined database. We have the following types of system defined views.
Information Schema View
In SQL Server we have twenty different schema views. These are used to display information of a database, like as tables and columns. This type of view starts with INFORMATION_SCHEMA and after this view name.
--Create a table create table Employee_Test ( Emp_ID int identity, Emp_Name varchar(55), Emp_Technology varchar(55), Emp_Sal decimal (10,2), Emp_Designation varchar(20) ) --To view detailed information of the columns of table Employee_Test SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Employee_Test'
Catalog Views were introduced with SQL Server 2005. These are used to show database self-describing information.
select * from sys.tables
Dynamic Management View
Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the database about the current state of the SQL Server machine. These values help the administrator to analyze problems and tune the server for optimal performance. These are of two types
Server-scoped Dynamic Management View
These are stored only in the Master database.
Database-scoped Dynamic Management View
These are stored in each database.
--To see all SQL Server connections SELECT connection_id,session_id,client_net_address,auth_scheme FROM sys.dm_exec_connections
User Defined Views
These types of view are defined by users. We have two types of user-defined views.
When we create a view on a single table, it is called a simple view.
--Now Insert data to table Employee_Test Insert into Employee_Test values ('Amit','PHP',12000,'SE'); Insert into Employee_Test values ('Mohan','ASP.NET',15000,'TL'); Insert into Employee_Test values ('Avin','C#',14000,'SE'); Insert into Employee_Test values ('Manoj','JAVA',22000,'SSE'); Insert into Employee_Test values ('Riyaz','VB',18000,'TH'); -- Now create view on single table Employee_Test create VIEW vw_Employee_Test AS Select Emp_ID ,Emp_Name ,Emp_Designation From Employee_Test
-- Query view like as table Select * from vw_Employee_Test
In the simple view we can insert, update, delete data. We can only insert data in a simple view if we have a primary key and all not null fields in the view.
-- Insert data to view vw_Employee_Test insert into vw_Employee_Test(Emp_Name, Emp_Designation) values ('Shailu','SSE') -- Now see the affected view Select * from vw_Employee_Test
-- Update data to view vw_Employee_Test Update vw_Employee_Test set Emp_Name = 'Pawan' where Emp_ID = 6 -- Now see the affected view Select * from vw_Employee_Test
-- Delete data from view vw_Employee_Test delete from vw_Employee_Test where Emp_ID = 6 -- Now see the affected view Select * from vw_Employee_Test
When we create a view on more than one table, it is called a complex view.
--Create another table create table Personal_Info ( Emp_Name varchar(55), FName varchar(55), DOB varchar(55), Address varchar(55), Mobile int, State varchar(55) ) -- Now Insert data Insert into Personal_Info values ('G.Chaudary','22-10-1985','Ghaziabad',96548922,'UP'); Insert into Personal_Info values ('B.S.Chauhan','02-07-1986','Haridwar',96548200,'UK'); Insert into Personal_Info values ('A.Panwar','30-04-1987','Noida',97437821,'UP'); Insert into Personal_Info values ('H.C.Patak','20-07-1986','Rampur',80109747,'UP'); Insert into Personal_Info values ('M.Shekh','21-10-1985','Delhi',96547954,'Delhi'); -- Now create view on two tables Employee_Test and Personal_Info Create VIEW vw_Employee_Personal_Info As Select e.Emp_ID, e.Emp_Name,e.Emp_Designation,p.DOB,p.Mobile From Employee_Test e INNER JOIN Personal_Info p On e.Emp_Name = p. Emp_Name
-- Now Query view like as table Select * from vw_Employee_Personal_Info
We can only update data in a complex view. We can't insert data in a complex view.
--Update view update vw_Employee_Personal_Info set Emp_Designation = 'SSE' where Emp_ID = 3 -- See affected view Select * from vw_Employee_Personal_Info
We make views for security purpose since it restricts the user to view some columns/fields of the table(s).
One more advantage of Views is, data abstraction since the end user is not aware of all the data present in the database table
In this article, I try to explain the view in SQL server with an example. I hope after reading this article your SQL views concepts will be strong. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.