Database Views (HL)

What Are Database Views?

A database view is a stored query that presents data from one or more tables in a specific way.

Views simplify complex queries, enhance security, and improve data consistency.

Types of Database Views

View Type	Description	Example Usage
Virtual View	A stored SQL query that dynamically presents data from tables	Used to simplify complex joins
Materialised View	A stored snapshot of data that is periodically refreshed	Used to improve performance on frequently accessed data

Virtual Views

A virtual view (the default view) is a dynamic query that retrieves data from underlying tables whenever accessed.

Advantages:

Data Independence: Changes in the underlying table do not affect the view.
Simplifies Queries: Reduces complexity for end users.
Security Control: Limits access to specific columns or rows.

Disadvantages

Performance Overhead: Each access to the view queries the underlying table(s), which may slow performance on large or complex datasets.
Limited Updatability: Views that include joins, aggregations, or DISTINCT may not support direct INSERT, UPDATE, or DELETE operations.
Dependency Issues: Structural changes in the underlying tables (e.g. renamed or removed columns) can break the view.

Example: Creating a Virtual View

            CREATE VIEW EmployeeView AS

            SELECT Name, Department, Salary

            FROM Employees

            WHERE Salary > 50000;

Example: Using a Virtual View

            SELECT Name, Department

            FROM EmployeeView

            WHERE Department = 'IT';

Materialised Views

A materialised view is a stored snapshot of data that is periodically refreshed.

Advantages:

Performance Improvement: Reduces query execution time.
Precomputed Data: Useful for complex calculations and reporting.
Reduces Database Load: Minimises repetitive queries on large datasets.

Disadvantages of Materialised Views

Stale Data: Data in materialised views can become outdated if not refreshed regularly.
Storage Usage: Since the data is stored physically, it requires additional disk space.
Maintenance Complexity: Automatic refreshes need to be scheduled and managed, adding to system overhead.

Example: Creating a Materialised View

            CREATE MATERIALIZED VIEW SalesSummary AS

            SELECT Department, SUM(Sales) AS TotalSales

            FROM Orders

            GROUP BY Department;

Example: Using a Materialised View

            SELECT Department, TotalSales

            FROM SalesSummary

            WHERE TotalSales > 100000;

Comparison: Virtual vs Materialised Views

Feature	Virtual View	Materialised View
Storage	No storage, dynamically retrieved	Stored as a snapshot
Performance	Slower, retrieves fresh data	Faster, precomputed data
Use Case	Hiding complexity, security filtering	Optimising frequent queries
Data Updates	Reflects real-time changes	Requires refresh to update

Why Are Views Important?

Hiding Complexity: Simplifies complex queries for users.
Data Consistency: Provides a single version of data for reporting.
Data Independence: Allows changes to base tables without affecting users.
Performance Optimisation: Materialised views improve query speed.
Security: Controls access to specific columns and rows.

Key Takeaways

Database views provide a way to simplify queries and enhance security.
Virtual views dynamically retrieve data without storage.
Materialised views store data for improved performance and faster queries.
Views help optimise reporting, hide complexity, and enforce data security.