Different Types of Views in SQL Server

Different Types of Views in SQL Server

15 Mar 2024
Intermediate
290K Views
26 min read

Views in SQL Server: An Overview

Views in SQL are virtual tables compiled at runtime. The data associated with views are not physically stored in the view but in the base tables of the view. In this SQL Server tutorial, we will understand what are views, how to create them, and their different types.

What is a View in SQL?

A View is just an SQL statement, and the associated data is not physically stored in the view but in the base tables of it. It 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 the view as a table. We can make an index, and trigger on the view.

Read More: SQL Server Interview Questions and Answers

In SQL Server we make views for security purposes 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.

A view may act as a filter on certain tables being referenced in the view. The query that is defined for the view can be from one or multiple different tables or other views in the current or other databases. The distributed queries may also be used to define the views in SQL servers that use the data from multiple data sources. It can be helpful in certain scenarios such as if you want to combine similarly structured data from the different servers, then each of them stores the data for a different region of your organization.

View in SQL

Read More: Basics of SQL Commands

Syntax to Create a View in SQL

The view can be created using the keyword "VIEW" followed by the view name as given below.


CREATE VIEW view_name AS    
SELECT column1, column2, ...    
FROM table_name    
WHERE condition;    

Types of Views

In SQL Server we have two types of views:

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

The system-defined views will expose the metadata of the database and they can be used to get all possible information about the instance of SQL Server or the database objects, columns, and contains, i.e. the "sys.databases" view to returning information about the user-defined databases which are available in the SQL server instance.

System Defined Views

Syntax to Create a System-Defined View in SQL

There is no specific keyword or additional syntax needed to indicate that the view is system-defined.


CREATE VIEW view_name AS    
SELECT column1, column2, ...    
FROM table_name    
WHERE condition;    

System-Defined View Example


SELECT *
FROM sys.tables;

This query retrieves information about all tables in the current database from the system catalog view sys.tables. The sys.tables view is a system-defined view in SQL Server that contains metadata about tables in the database, such as their names, object IDs, schema names, and creation dates.

Use Cases of System-Defined View in SQL

  • Accessing Metadata: These views allow users to query and retrieve information about the structure and configuration of the database.
  • Monitoring Database Performance: DBAs and system administrators can use these views to identify performance bottlenecks, optimize query execution plans, and troubleshoot performance issues.
  • Managing Security: System-defined views can assist in managing security and access control within the database.
  • Auditing and Compliance: System-defined views can be used for auditing and compliance purposes by tracking changes to database objects, user activities, and system events.
  • Generating Reports: System-defined views can serve as data sources for generating reports and dashboards within the database management system or external reporting tools.

We have the following three types of system-defined views.

  1. Information Schema View

    In SQL Server we have twenty different schema views. These are used to display information in a database, like tables and columns. This type of view starts with INFORMATION_SCHEMA and after this view name.

    To create an Information Schema View, you don't need to define or create it manually, as it's already provided by the database system. You can just query these views to retrieve metadata about the database objects.

    Syntax to query the Information Schema View

    
    SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'your_schema_name' AND TABLE_NAME = 'your_table_name';
    

    Example

    
    --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 on the columns of table Employee_Test

    
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME='Employee_Test'
    

    Information Schema View

    Use Cases of Information Schema View

    • Data Dictionary Queries: It allows users to query metadata about database objects, such as tables, columns, and constraints.
    • Schema Comparison: Information Schema Views enable users to compare the schema of different databases or database versions.
    • Dynamic SQL Generation: They provide a dynamic way to generate SQL statements dynamically based on the database's schema.
    • User Privilege Management: This includes information about user privileges and permissions granted within the database.
    • Database Documentation: They serve as a valuable resource for documenting database structures and configurations.
    • Data Profiling and Analysis: By querying these views, analysts can gather statistical information about the distribution of data values, data types, or column cardinality, helping to identify data quality issues or optimization opportunities.
  2. Catalog View

    Catalog Views were introduced with SQL Server 2005. These are used to show database self-describing information. Catalog views provide an efficient way to obtain, present, and transform custom forms of information.

    Example

    
    select * from sys.tables
    

    Catalog View

    Use Cases of Catalog View

    • Schema Exploration: Developers and administrators can use catalog views to understand the database's layout and relationships between objects.
    • Dependency Analysis: Users can query catalog views to identify dependencies between tables, views, stored procedures, functions, and other objects.
    • Data Dictionary Generation: Catalog views are often used to generate data dictionaries or documentation about the database schema.
    • Security Auditing: Catalog views provide information about database security settings, permissions, and user access rights.
    • Database Monitoring: Catalog views enable database monitoring and management by providing insights into the database's operational status and resource utilization.
  3. Dynamic Management View

    Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information about the database's current state of the SQL Server machine. These values help the administrator to analyze problems and tune the server for optimal performance.

    These are two types of Dynamic Management Views:

    1. Server-Scoped Dynamic Management View

      These are stored only 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.

      Syntax to Create Server-Scoped Dynamic Management View

      
      CREATE VIEW [schema_name].[view_name]
      AS
      SELECT column1, column2, ...
      FROM sys.dm_exec_query_stats
      WHERE ;
       

      Example

      
      SELECT 
          type,
          name,
          memory_node_id,
          pages_kb
      FROM 
          sys.dm_os_memory_clerks
      ORDER BY 
          pages_kb DESC;
      

      In the above code,

      • We're querying the "sys.dm_os_memory_clerks" DMV to retrieve information about memory clerks.
      • We're selecting columns such as the memory clerk type, name, memory node ID, and the amount of memory allocated to each memory clerk in kilobytes (KB).
      • We're ordering the results by the amount of memory allocated to each memory clerk in descending order to identify memory-intensive components.

      Use Cases of Server-Scoped Dynamic Management View

      • Performance Tuning: Server-scoped DMVs offer information on server resource usage, query execution statistics, and wait statistics.
      • Monitoring: By regularly querying server-scoped DMVs, administrators can monitor server health and detect issues such as resource contention, long-running queries, or excessive resource utilization.
      • Capacity Planning: They provide insights into resource consumption trends over time, helping administrators anticipate future capacity requirements.
      • Security Auditing: DMVs such as sys.dm_exec_connections and sys.dm_exec_sessions provide information about current connections to the server, including login names, IP addresses, and application names.
      • Troubleshooting: By examining query execution plans, identifying blocking processes, or analyzing memory usage patterns, administrators can quickly identify and resolve issues to minimize downtime.
      • Capacity Optimization: Through analysis of DMV data, administrators can identify underutilized resources and optimize resource allocation to maximize server efficiency.
      • Backup and Restore Management: Administrators can use this data to optimize backup strategies, monitor backup performance, and ensure data integrity during restore operations.
    2. Database-Scoped Dynamic Management View

      Database-scoped DMVs are stored in each 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.

      Syntax to Create Database-Scoped Dynamic Management View

      
      USE YourDatabaseName;
      GO
      
      CREATE VIEW dbo.YourViewName
      AS
      SELECT
          -- Columns from dynamic management views/functions
      FROM
          -- Dynamic management views/functions
      GO 

      Example

      
      --To see all SQL Server connections
      SELECT connection_id,session_id,client_net_address,auth_scheme
      FROM sys.dm_exec_connections 
      

      Dynamic Management View

      Use Cases of Database-Scoped Dynamic Management View

      • Index Maintenance: These DMVs can be used to monitor the fragmentation levels of indexes within a database.
      • Query Performance Tuning: By analyzing query execution plans and statistics using These DMVs, database administrators can identify inefficient queries, missing indexes, or opportunities for query optimization within a specific database.
      • Schema Analysis: Database-scoped DMVs can be utilized to analyze the schema of a database, including table and index definitions, column properties, and relationships.
      • Compliance and Auditing: Database-scoped DMVs facilitate compliance with regulatory requirements by providing visibility into database activities, access controls, and data changes.
      • Backup and Restore Operations: Database-scoped DMVs offer visibility into backup and restore operations within a database, including backup history, completion status, and backup sizes.

2. User Defined Views

These types of views are defined by users. We have two types of user-defined views.

There are two types of User Defined views:

  1. Simple View

    When we create a view on a single table, it is called a simple view.

    Syntax to Create a Simple View

    
    CREATE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;
    

    Example

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

    User Defined Views

    In the simple view, we can insert, update, and 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 
    

    Use Cases of Simple View

    • Data Abstraction: Simple views can abstract away complex underlying table structures, presenting users with a simplified and more intuitive representation of the data.
    • Security and Access Control: Views can be used to enforce security policies by restricting access to sensitive data or columns.
    • Data Simplification: Views can be used to filter, aggregate, or transform data to meet specific reporting or analysis requirements.
    • Data Integration: Views can integrate data from multiple tables or databases, providing a unified view of related information.
    • Query Simplification: Views can simplify complex queries by encapsulating commonly used joins or filters.
    • Application Development: Applications can interact with views instead of directly accessing underlying tables, reducing coupling and improving maintainability.
  2. Complex View

    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 purposes since it restricts the user from viewing some columns/fields of the table(s).

    Use Cases of Complex View

    • Data Normalization: Views can normalize denormalized data, allowing users to interact with normalized data structures without directly modifying underlying tables.
    • Data Transformation: Complex views can transform data into different formats or structures to meet specific application requirements.
    • Dynamic Data Filtering: Complex views can apply dynamic filtering criteria based on user input or session context.
    • Join Optimization: Complex views can optimize join operations by precomputing joins between multiple tables or views.
    • Dynamic Data Filtering: Complex views can apply dynamic filtering criteria based on user input or session context.

Note

  1. We make views for security purposes since it restricts the user from viewing some columns/fields of the table(s).
  2. One more advantage of Views is, data abstraction since the end user is not aware of all the data present in the database table.

Advantages of Using Different Types of Views

  • Simplified Data Access: All types of views, whether simple or complex, provide a simplified interface for accessing data.
  • Data Security: Views can enhance data security by restricting access to sensitive data or columns.
  • Data Masking and Anonymization: Views can mask sensitive data or anonymize personally identifiable information (PII) before exposing it to end-users or applications.
  • Data Analysis and Reporting: Views can facilitate data analysis and reporting by providing predefined views of the data tailored to specific reporting requirements.
  • Query Simplification: Instead of writing complex SQL queries repeatedly, users can query the view, which encapsulates the complexity and provides a simplified interface.

Best Practices for Using Different Types of Views

  1. Simple Views
    • Keep Views Simple
    • Promote Reusability
    • Consider Security
  2. Complex Views
    • Document Complexity
    • Optimize Performance
    • Test Thoroughly
  3. Materialized Views
    • Evaluate Refresh Strategies
    • Balance Freshness and Performance
    • Consider Storage Requirements
Summary

In SQL Server, a view is similar to the virtual table whose values are defined by the user-written queries. In other words, we can say that a view is a name given to a query that can be used as a collection of multiple columns of the table that can be referenced for showing the collection of data from multiple sources.

The primary usage of the view in SQL Server is to prevent security breaches and it does it for users from seeing specific columns and rows from tables randomly. The view will only show the data returned by the specific query declared when the view was created. The rest of the information is completely hidden from the end-user, unlike the normal table definition. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

FAQs

Q1. What are the different types of views in SQL?

In SQL Server we have two types of views:
1. System Defined Views
2. User Defined Views

Q2. What is a view in SQL Server?

Views in SQL are virtual tables compiled at runtime. The data associated with views are not physically stored in the view but in the base tables of the view.

Q3. Do views store data?

Views do not store data except for indexed views.

Q4. Does a view require storage in a database?

No, a view does not require any storage in a database because it does not exist physically.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Courses
  • 750+ Hands-On Labs
  • 300+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A
  • 10+ Real-world Projects
  • Career Coaching
  • Email Support
Upto 66% OFF
KNOW MORE..

To get full access to all courses

Accept cookies & close this