Writing complex SQL queries and securing database access are the challenges that Database Administrators and Users always face, and these queries can become very complicated. Using a proxy over the original table helps in simplifying such queries. Also, there are cases in which the administrator wants to limit direct access to the database. For both these circumstances, views can be used.
What is a View?
SQL has a special version of tables called View, which is a virtual table that is compiled in runtime. A View is just an SQL statement, and the data associated with it is not physically stored in the view but is stored in the base tables of it.
It can contain all the rows and columns of a table or only a few selected rows and columns if there is a need to restrict the access. Depending on the written SQL query used to create the view, it can be created from one or many tables.
Views can be used to structure data in ways for users to find it natural, simplify complex queries, restrict access to data, and summarize data from several tables to create reports.
There are different aspects related to managing views, which are defined here.
Creating view: Views can be created using the “create view” statement. The view is defined by a query that references materialized views, tables, or other views.
Renaming view: Views can be renamed, and it should be ensured that all objects that reference the old name of the view now should have a new name.
Listing views: All the views in the SQL Server Database can be listed by querying the system catalog view.
Removing view: Using the “drop view” statement, an existing view can be removed.
Read: SQL vs PlSQL
Types of Views in SQL
There are two types of views in the SQL Server, namely System Defined Views and User Defined Views. This section contains a description of these two types.
System Defined Views
The System Defined Views are predefined views that already exist in the SQL Server database, such as Tempdb, Master, and temp. Each of the databases has its own properties and functions.
The template database for all User Defined views is from the Master database. It contains many predefined views that are templates for tables and other databases. It contains nearly 230 of the predefined views.
System Defined Views will be automatically attached to all User Defined databases. And these provide information about the database, tables, and all the properties of the database and tables. There are three types of System defined views, Information Schema, Catalog View, and Dynamic Management View.
There are twenty different schema views in the SQL server. They are used to display the physical information of the database, such as tables, constraints, columns, and views. This view starts with INFORMATION_SCHEMA and followed by the View Name. INFORMATION_SCHEMA.CHECK_CONSTRAINTS is used to receive information about any constraint available in the database.
A constraint is used on a particular column in a table to ensure that certain data rules are followed for the column. INFORMATION_SCHEMA.COLUMNS is used to receive information about the table columns such as table name, column name, the position of the column, default value, etc. To return the views present in the current database, INFORMATION_SCHEMA.VIEWS is used.
These are used to return information used by the SQL server. Catalog views provide an efficient way to obtain, present, and transform custom forms of information. But they do not include any information about backup, replication, or maintenance plans, etc. These views are used to access metadata of databases, and the names and column names are descriptive, helping a user to query what is expected.
Dynamic Management View
These were introduced in the SQL server in 2005. The administer can get information about the server state to diagnose problems, monitor the health of the server instance, and tune performance through these views. The Server-scoped Dynamic Management View is only stored in the Master database, whereas the Database-scoped Dynamic Management View is stored in each database.
User Defined Views
These are the types of views that are defined by the users. There are two types under User Defined views, Simple View and Complex View.
These views can only contain a single base table or can be created only from one table. Group functions such as MAX(), COUNT(), etc., cannot be used here, and it does not contain groups of data.
By using Simple View, DML operations can be performed. Insert, delete, and update are directly possible, but Simple View does not contain group by, pseudocolumn like rownum, distinct, columns defined by expressions. Simple view also does not include NOT NULL columns from the base tables.
These views can contain more than one base table or can be constructed on more than one base table, and they contain a group by clause, join conditions, an order by clause. Group functions can be used here, and it contains groups of data. Complex views cannot always be used to perform DML operations.
Insert, delete, and update cannot be applied directly on complex views. But unlike Simple Views, Complex Views can contain group by, pseudocolumn like rownum, distinct, columns defined by expressions. NOT NULL columns can be included in complex views while they are not selected by the Simple View.
There are other views, such as Inline View and Materialized View. The inline view is based on a subquery in FROM clause, the subquery creates a temporary table, and this simplifies the complex query.
These views are used to write complex SQL queries without the join and subqueries operations. The materialized view stores the definition and even the data. Replicas of data are created by storing it physically. This view reduces the processing time for regenerating the whole data.
This article describes the types of views in SQL. A View in SQL is defined and explained in detail, and the different ways in which Views are managed is also defined. The different types of views in SQL, such as System Defined Views and User Defined Views, are described in detail along with the various subtypes under each type.
If you’re interested to learn more about full-stack software development, check out upGrad & IIIT-B’s PG Diploma in Full-stack Software Development which is designed for working professionals and offers 500+ hours of rigorous training, 9+ projects, and assignments, IIIT-B Alumni status, practical hands-on capstone projects & job assistance with top firms.