Types of Views in SQL | Views in SQL [2023]

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. Here is a look into understanding what a view is in terms of SQL and also elaborating on the varied kinds and formats.

Check out Advanced Certification in Cloud Computing

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.

Also, check out our free courses to get an edge over the competition.

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.

Explore Our Software Development Free Courses

Check out Full Stack Development Bootcamp

In order to understand the 4 views used in database, it is essential to clarify what a database view is. The database view is a type of database subset based on any query running on a single or multiple database tables. All of the 4 views used in database get saved in a database, as are the named queries. These 4 views used in database are used for saving frequently used as well as complex queries.

Understanding Broad Types Of Views

There are two types of views for a database. These are called dynamic and static views. The form view types contain data from a single or two tables at the most. This automatically includes all columns from a specific table or from several tables.

The dynamic views from the types of views get updated in an automated manner when the related objects or the extended objects get created or changed. This is a classic characteristic. These views can contain data from multiple tables, and all required column from such tables needs to be specified in SELECT as well as under the WHERE clauses of the static view. These static views need to be manually updated on creation or change in the related objects or the extended objects.

Managing Views

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.

Explore our Popular Software Engineering Courses

Read: SQL vs PlSQL

Enrol in Online Software Development Courses from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

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.

In-Demand Software Development Skills

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.

Information Schema

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.

upGrad’s Exclusive Software and Tech Webinar for you –

SAAS Business – What is So Different?

Catalog View

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.

Simple 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.

Read our Popular Articles related to Software Development

Complex View

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.

Read: Exciting SQL Project Ideas & Topics

More Details On View In SQL

When you are in the process of creating any dynamic view using data from a couple of tables, you need to ensure that these tables have the same PRIMARYKEYCOLSEQ column or at least contain unique indexes using the same column name in the same order. This is important in understanding how view in SQL works.

Another thing about the view in SQL is that for a multitenancy environment, a global administrator generally creates the initial database views. These views in SQL stand as a part of default data provided to the tenants. This tenant ID needs to get added to SELECT and the WHERE clauses for static views in SQL. This is done to ensure that all tenant-specific views used in database are created. For the dynamic views used in database, the same needs usage for creating tenant-specific views used in database for the tenants having extended attributes. The views that are static don’t support extended attributes.

Conclusion

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 Executive PG Program 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.