In this article, We will learn about the different types of views in SQL Server with multiple examples. We will also learn how to create, modify, update, and delete views in SQL Server.
What is a view in SQL?
Views in SQL Server are simply virtual tables that contain data from one or multiple tables. It does not store any data and does not physically exist in the database. We can create an index, and trigger on the view.
Views in SQL Server provide a security mechanism that allows users to access data through the view without having direct access to the columns/fields of the actual tables.
Key Points About the View In SQL Server
- A View in SQL Server is a virtual table that doesn’t have a physical existence in the database.
- A view displays only selected columns/fields from one or more tables.
- The view only stores
selectquery statements and does not store actual data in the underline base table.
- Views in SQL can be created by using SQL
- A view is a logical entity, while a table in SQL is a physical entity.
- Views can be used to provide a backward-compatible interface to simulate tables whose schema has changed.
- It helps to simplify complex SQL queries, prevent users from accessing the actual data in the base table
Syntax to create a view in the SQL server
The following is the syntax to create a view in the SQL server.
Views in SQL Server can be used to provide row and column level security. We can allow users to access only the selected columns of the table instead of accessing the entire record in the table.
What are the types of views in SQL?
In SQL Server, there are two main types of views available, namely, system-defined views and user-defined views.
- System Defined Views
- Information Schema View
- Catalog View
- Dynamic Management View
- User Defined Views
- Simple View
- Complex View
- System Defined Views
Let’s first look at the user-defined view.
User-Defined Views in SQL
There are two types of user-defined views, namely simple views and complex views.
01. Simple View In SQL
In SQL, views created on a single table are called simple views.
Simple views support DML operations. If the base table contains a primary key and no empty columns, we can insert, update or delete records.
Simple view example
The following is an example of creating a simple view on the SQL server.
Let’s create a simple view with small records on the
-- Creating a new table "tblEmployee". CREATE TABLE tblEmployee ( [Id] INT PRIMARY KEY, [Name] NVARCHAR(30), [Gender] VARCHAR(10), [City] VARCHAR(50), [DeptId] INT ); --Inserting data into the "tblEmployee" table. INSERT INTO tblEmployee VALUES (101,'Shekh Ali','Male','Delhi', 1), (102,'Mark Adam','Male','New York', 1), (103,'Julie Folden','Female','Chicago', 2); -- SQL Create a Simple View on base table "tblEmployee" CREATE VIEW [SimpleViewOnTblEmployee] AS SELECT Id, Name, Gender, City, DeptId FROM tblEmployee;
Let’s query view to show the records like a table.
-- Show records from a simple view like a table. SELECT * FROM [SimpleViewOnTblEmployee];
Insert operation on Simple view in SQL
In a simple view, we can insert, update or delete records. The following is a query to insert a new record in a simple view.
-- Insert record into Simple view INSERT INTO [SimpleViewOnTblEmployee] VALUES (104,'Salim Khan','Male','Delhi', 1); -- Let's see the affected view SELECT * FROM [SimpleViewOnTblEmployee];
Update operation on simple view in SQL
When we execute the following update query, It will successfully update the record from the view. Let’s update the record whose id is 104.
-- Update record into Simple view UPDATE [SimpleViewOnTblEmployee] SET Name='Daniel' WHERE Id = 104;
To check the updated record, a select query against the simple view “SimpleViewOnTblEmployee” is shown below.
-- Select record from the sample view SELECT * FROM [SimpleViewOnTblEmployee] ;
Delete operation on simple view in SQL
Let’s execute the following SQL command to delete the record whose Id is 102 from the simple view.
-- Delete data from view "SimpleViewOnTblEmployee" Delete from [SimpleViewOnTblEmployee] where Id = 102 ; -- Let's see the affected view after delete operation Select * from [SimpleViewOnTblEmployee] ;
In the above image, we can see that the record whose Id was 102 is deleted successfully.
02. Complex View In SQL
A View created on more than one base table is known as a Complex View . It doesn’t allow to perform
DML operation. A complex view is also known as a non-updatable or static view.
The complex view can contain a
group by clause,
order by clause,
Aggregate Functions, or
-- Create a new [DEPARTMENT] table. CREATE TABLE DEPARTMENT ( [Id] [int] PRIMARY KEY NOT NULL, [DeptName] [nvarchar](50) NULL, ); -- INSERT RECORD INTO THE DEPARTMENT TABLE. INSERT INTO DEPARTMENT VALUES(1,'IT'),(2,'HR'),(3,'FINANCE'); -- Show record from the department table. SELECT * FROM DEPARTMENT; -- Now create view on two tables [tblEmployee] and [Department] CREATE VIEW vw_tblEmployee_Department AS SELECT e.Id, e.Name,e.Gender,e.City,d.DeptName FROM tblEmployee e INNER JOIN Department d ON e.DeptId = d.Id;
Now run the below query to show the records from the [vw_tblEmployee_Department] complex view.
-- Run the below query to show the records from the complex view [vw_tblEmployee_Department] . SELECT * FROM vw_tblEmployee_Department;
Update Complex View in SQL
Note: We are allowed to update the existing records in a complex view, but can’t insert the new records.
If a complex view is based on multiple tables, might be it will not allow updating the base table correctly. To update the complex view, instead of trigger can be used.
Following is the SQL query to update the complex view.
-- Query to update [City] in the in the complex view [vw_tblEmployee_Department]. UPDATE vw_tblEmployee_Department set City ='Mumbai' Where Id = 101;
Once we run the above query, we can see that the City is updated from “Delhi” to “Mumbai” for Id 101.
System Defined Views In SQL
In SQL Server, the System defined views are predefined Views that already exist in the master database.
The master database generally serves as the template database for all other user-defined databases on the SQL server. It contains almost 230 predefined views.
The following are three system-defined views, namely the information schema view, the catalog view, and the dynamic management view.
Information Schema View In SQL Server
In SQL Server, there are approx 21 different Information schema views are available. These types of views start with INFORMATION_SCHEMA as a prefix and are used to provide information about the database objects such as tables, columns, domains, check constraints, etc.
Following is the SQL statement to view detailed information on the columns of table tblEmployee.
-- To view detailed information of the columns of table tblEmployee SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblEmployee'; -- OR SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblEmployee';
The following INFORMATION_SCHEMA.CHECK_CONSTRAINTS is used to return the information about the constraints of a table.
-- Return information about the constraints on a table SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME ='tblEmployee';
The following INFORMATION_SCHEMA.VIEWS will return all the views present in the current database.
--This INFORMATION_SCHEMA.VIEWS will return all the views present in the current database. SELECT * FROM INFORMATION_SCHEMA.VIEWS ;
What are Catalog Views in SQL Server?
As per MSDN: Catalog views are used to return information used by the SQL server database engine. Catalog views are mainly used to provide information about a database metadata and its objects such as tables and views.
The sys.objects catalog view is referred to as the base view, and the sys.tables view is called the derived view. The
sys.tables catalog view in the SQL server returns the columns that are specific to tables and also all the columns that the
sys.objects catalog view returns.
sys.objects catalog view returns rows of objects other than tables, such as stored procedures and views. Once a new table is created, the metadata for the table is returned in both
Use of catalog views in SQL Server:
- Catalog views provide information about the metadata of SQL databases.
- Catalog views return rows of objects other than tables, such as stored procedures and views.
- Catalog views provide descriptive column names, which might be very helpful for users to understand the data.
-- Catalog view example for tables: SELECT * FROM sys.tables; -- Catalog view example for database SELECT name, database_id, compatibility_level,* FROM sys.databases WHERE name LIKE 'master%';
What Is Dynamic Management Views In SQL Server?
The Dynamic Management Views were introduced in SQL Server 2005. These Views provide the administrator information about the server state to diagnose problems and monitor the health and the current state of the SQL server machine.
These views help the administrator to analyze database-related problems and tune the SQL server for optimal performance.
These are two types of Dynamic Management Views.
01. Server-Scoped Dynamic Management View: These types of views are stored only in the Master database.
02. Database Scoped Dynamic Management View: These types of views are stored in each database.
--This returns current session information SELECT login_name ,COUNT(session_id) AS session_count FROM sys.dm_exec_sessions GROUP BY login_name; --To see all SQL Server connections SELECT connection_id,session_id,client_net_address,auth_scheme FROM sys.dm_exec_connections ;
Q: How do you change the view in SQL Server?
We can rename the view by using the system-defined stored procedure called sp_rename. The following is the syntax of the sp_rename command:
Sp_Rename OldViewName , NewViewName;
Q: How do I alter a SQL view?
The following is the syntax to alter an existing view.
ALTER VIEW ViewName
SELECT Column1, Column2, Column3 …
Q: How do you drop a view from a database?
A view in the SQL server can be deleted by using the DROP VIEW statement.
SQL DROP VIEW Syntax:
DROP VIEW view_name;
Q: How to Get Information About a View in SQL Server?
The sp_helptext stored procedure returns the definition of a user-defined object such as a view or table in an SQL server.
Syntax to get Information About a View in SQL Server
Execute sp_helptext ‘View_Name’;
Q: How do you refresh a view in SQL Server?
In SQL Server, Once the dependent tables of a view have been altered, it is required to refresh or update the view definition as well by using the sp_refresh system-defined stored procedure.
— Syntax to refresh a view in SQL Server
Exec sp_refreshview ‘View_Name’;
In this article, we discussed different types of views in SQL, such as System Defined Views and User Defined Views.
I hope you enjoyed this post. If you have any questions or suggestions, please leave them in the comments section.
References w3schools: SQL Views
- Stored Procedure in SQL Server With Examples
- SQL Delete Cascade – SQL query to delete parent-child referenced rows
- CREATE, ALTER, AND DROP DATABASE IN SQL SERVER
- Primary Key Constraint In SQL Server
- SQL Comparison Operators
- SQL Server Trigger Update, Insert, Delete Examples