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

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.

Learn to build applications like Swiggy, Quora, IMDB and more

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.

Check out Full Stack Development Bootcamp

Listing views: All the views in the SQL Server Database can be listed by querying the system catalog view.

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.

Read: Rename Column Name in SQL

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.

Removing view: Using the “drop view” statement, an existing view can be removed.

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

Learning about view and its types in DBMS is essential for any database administrator or backend developer. Once you learn how many types of views are there in DBMS and are able to use SQL to effectively manage your data for various operations, you will become get one step closer to to becoming an expert in database management.

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.

The two types of views in SQL server offer many essential views that fall under them with unique features. System Defined Views, provided by the database system, offer insights into database structure and system performance.

User Defined Views, created by users or administrators, allow for customized virtual representations of data, enhancing query simplicity and security. Both types contribute to the flexibility and usability of SQL Server databases, empowering users with a range of tools for data exploration and management. Let us learn about these two types of views in SQL server in more detail.

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.

The INFORMATION_SCHEMA.TABLES view is employed to obtain information about tables within the current database. It includes details such as the table name, table type (base table or view), and the schema to which the table belongs.

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS view is utilized for retrieving information about constraints applied to tables. It presents details such as the constraint name, constraint type (e.g., primary key, foreign key), and the associated table.

For insights into columns that are part of key constraints, the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view provides information about the constraint name, table name, and the column included in the constraint.

Focused on relationships between tables, the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view delivers information about referential constraints. It includes details about the foreign key and primary key involved in the relationship.

For stored procedures, functions, and triggers, the INFORMATION_SCHEMA.ROUTINES view supplies information such as the routine name, routine type, and the schema to which the routine belongs.

The INFORMATION_SCHEMA.PARAMETERS view is utilized to gather information about parameters associated with stored procedures and functions. It includes details such as parameter name, data type, and the routine to which the parameter belongs.

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.

Catalog views in SQL Server serve as a vital component for database administrators, developers, and analysts, offering a structured and user-friendly approach to accessing essential metadata. Unlike Dynamic Management Views (DMVs), which focus on runtime server statistics, catalog views concentrate on providing a static snapshot of the database’s structure and characteristics.

One notable advantage of catalog views is their efficiency in delivering information. They provide a reliable means of obtaining and presenting custom forms of data without the need for complex queries or extensive parsing. This efficiency is particularly valuable when users need to explore the database schema, understand object dependencies, or analyze the underlying structure of the data.

Despite their usefulness, it’s important to recognize that catalog views have specific limitations. Notably, they do not cover information related to administrative aspects such as backup history, replication status, or maintenance plans. Administrators seeking insights into these areas will need to turn to other system views and tools dedicated to administrative tasks.

The user-friendly nature of catalog views stems from their descriptive naming conventions and column names. This feature significantly enhances the usability of these views, as users can intuitively understand the purpose of each view and the information it provides. For instance, sys.objects is a commonly used catalog view that provides details about all objects in the database, including tables, views, and procedures.

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.

Dynamic Management Views (DMVs) play a crucial role in providing administrators with valuable insights into the internal workings of SQL Server. These views offer a dynamic and real-time perspective on server and database activities, allowing administrators to make informed decisions for performance tuning, monitoring, and problem diagnosis.

Server-scoped DMVs are stored exclusively in the Master database. These views provide a comprehensive overview of the entire SQL Server instance, including system-wide performance metrics, resource utilization, and current server state.

On the other hand, Database-scoped DMVs are stored in each individual database. These views focus on the specific activities and performance metrics within a particular database, offering insights into query execution, index usage, and other database-specific details. 

DBAs (Database Administrators) leverage DMVs for various purposes, such as identifying performance bottlenecks, troubleshooting issues, and optimizing query execution plans. For instance, sys.dm_exec_query_stats provides statistics on the execution of queries, helping administrators pinpoint inefficient queries and optimize them for better performance.

DMVs are read-only and do not impact the system’s performance. However, users must have the necessary permissions to query these views. Additionally, as SQL Server evolves with each version, new DMVs may be introduced, offering enhanced capabilities and insights.

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.

Simple Views lack the capability to include a GROUP BY clause. This means that the data presented in the view is not organized into groups based on specific column values. Any grouping or aggregation must be handled at the level of the base table. Pseudocolumns such as ROWNUM, which are often used for row identification or filtering, cannot be directly included in a Simple View. Any requirements for such pseudocolumns need to be addressed at the level of the base table or through other means.

The DISTINCT keyword, commonly used to retrieve unique values from a result set, is not applicable within a Simple View. If distinct values are needed, alternative approaches or more complex views may be necessary. Simple Views do not support the inclusion of columns defined by expressions. Columns that involve calculations or complex expressions need to be derived from the base table itself, as they cannot be directly incorporated into the view definition. While Simple Views provide a convenient way to interact with data, they do not consider the NOT NULL constraints of columns in the base table. Users must be aware that NULL values might be present in the view, even for columns that are designated as NOT NULL in the original table.

The primary advantage of Simple Views lies in their simplicity and direct representation of data from a single table. This makes them suitable for scenarios where straightforward access to the underlying data is essential, and the absence of complex features is not a limitation.

Simple Views are particularly useful in scenarios where users require direct access to the data for basic operations like data entry, modification, or retrieval. They are well-suited for applications and interfaces that demand a clear and uncomplicated representation of the underlying information.

Simple Views, being based on a single table, may offer better performance in certain situations compared to complex views involving multiple tables. This simplicity can be advantageous, especially when dealing with large datasets and frequent data manipulation operations.

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.

Unlike Simple Views, Complex Views can involve more than one base table. This allows for the integration of data from different sources, providing a more comprehensive view for analytical purposes. Complex View in SQL supports the use of the GROUP BY clause and join conditions. This enables the aggregation of data based on specified criteria and the ability to combine information from multiple tables using various types of joins.

The ORDER BY clause can be utilized in Complex Views to sort the result set based on specified columns and sorting orders. This adds flexibility to how the data is presented to users. Group functions such as MAX(), COUNT(), and others can be applied within Complex Views. This facilitates the analysis and summarization of data, providing valuable insights into the information presented by the view.

While Complex Views offer powerful analytical capabilities, direct Insert, Delete, and Update operations may not always be applicable. This limitation is due to the complexity introduced by involving multiple tables and potentially complex join conditions. Unlike Simple Views, Complex Views can include advanced features such as GROUP BY, pseudocolumns like ROWNUM, DISTINCT, and columns defined by expressions. This makes them suitable for scenarios requiring sophisticated data manipulation and analysis.

Complex Views can include NOT NULL columns from the base tables. This provides a more accurate representation of the underlying data by respecting the constraints defined in the original tables.

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.

Additional Tips for Creating Views in SQL

Here are some essential tips for using the different types of views in SQL:

• Utilize CREATE VIEW SQL Server to define virtual tables for simplified query execution and enhanced data accessibility.
• Explore the efficiency gains of a materialized view in Snowflake (traditional database view), which stores precomputed results for optimized query performance.
• Enhance query efficiency in SQL Server by employing materialized view SQL Server through the CREATE MATERIALIZED VIEW statement.
• Adapt existing views seamlessly with ALTER VIEW in SQL, ensuring flexibility in response to evolving data requirements.
• Leverage the strategic advantage that you gain when you create materialized view SQL to build optimized views that store and deliver precomputed results.

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.

So if you were wondering “SQL server has mainly how many types of views?”, this article should have given you some more closure.

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.