Views On Sql

A view in sql are virtual tables, having saying that it does not store any data on disk like tables but it define sql statement and that retrieve latest data from one or more tables. simply it is a stored query that present data from one or more tables. There are mainly two types of views on sql which are,

1. User Defined Views
2. System Defined Views

User Defined Views

a. Standard View

these are the most basic types of view, they are created using select statement and do not store data themselves. they just display data from one or more tables.

let suppose you have ‘Employees’ table and wanna access data as wiew, you can create view as,

CREATE VIEW EmployeeView AS
   SELECT EmployeeID, FirstName, LastName, Department
   FROM Employees;

here it create view named as EmployeeView and you can access data as

SELECT * from EmployeeView;s

b. Complex View

Complex views involve more advanced SQL operations such as joins, subqueries, and aggregations. They may involve multiple tables and provide a more complex data representation

let suppose we have ‘Sales’, ‘Products’ and ‘Customers’ tables and wanna access customers, products and sales datas.

CREATE VIEW SalesSummary AS
     SELECT s.SaleID, s.SaleDate, p.ProductName, c.CustomerName
     FROM Sales s
     JOIN Products p ON s.ProductID = p.ProductID
     JOIN Customers c ON s.CustomerID = c.CustomerID;

c. Materialized Views

Materialized views store the result of the query physically. They can be refreshed periodically to update the stored data. This is useful for improving performance on complex queries by avoiding re-computation.

CREATE MATERIALIZED VIEW SalesSummary AS
    SELECT ProductID, SUM(Quantity) AS TotalQuantity
    FROM Sales
    GROUP BY ProductID;

d. Indexed Views (SQL Server)

Indexed views (also known as materialized views in other databases) in SQL Server have indexes created on them, which allows the query results to be stored and indexed for faster retrieval.

CREATE VIEW SalesSummary WITH SCHEMABINDING AS
   SELECT ProductID, SUM(Quantity) AS TotalQuantity
   FROM dbo.Sales
   GROUP BY ProductID;

CREATE UNIQUE CLUSTERED INDEX IDX_SalesSummary ON SalesSummary (ProductID);

e. Partitioned Views

Partitioned views are used to horizontally partition data across multiple tables. This is useful for managing large datasets by splitting data into smaller, more manageable pieces.

CREATE VIEW AllCustomers AS
  SELECT * FROM CustomersNorth
  UNION ALL
  SELECT * FROM CustomersSouth
  UNION ALL
  SELECT * FROM CustomersEast
  UNION ALL
  SELECT * FROM CustomersWest;

System Defined Views

System-defined views, also known as system views, are views provided by the database management system (DBMS) to expose metadata and information about the database schema, objects, and state. These views are typically read-only and are used by database administrators and developers to understand and manage the database environment. Different DBMS platforms have their own sets of system views. Here are some examples from various database systems:

a. SQL Server System Views

SELECT * FROM sys.tables;
SELECT * FROM sys.columns;

b. MySQL System Views

SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

c. PostgreSQL System Views

SELECT * FROM pg_tables;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

Performance Considerations:

a. Indexing Underlying Tables: Improves performance of standard views by optimizing the queries on the base tables.

b. Materialized Views: Enhance performance by storing precomputed results, particularly for complex and resource-intensive queries.

c. Indexed Views: In SQL Server, indexed views can significantly speed up query performance by maintaining indexed results.

Use Cases :

a. Reporting: Simplify reporting queries by creating views that aggregate and join data from multiple tables.

b. Data Integration: Use views to present a unified interface for querying data from disparate sources.

c. Access Control: Limit user access to sensitive data by exposing only necessary columns and rows through views.

Limitations :

a. Read-Only: Standard views are often read-only unless certain conditions are met.

b. Maintenance: Views must be maintained alongside the underlying tables to ensure they reflect the current state of the data.

c. Performance Overhead: While views can simplify queries, complex views may introduce performance overhead if not managed properly.

Conclusion

Views are an essential tool in SQL for managing, abstracting, and securing data. They offer a versatile way to present and interact with data, catering to various use cases from simple data abstraction to complex data integration and reporting. Understanding the different types of views and their appropriate use cases is crucial for effective database design and management.