Microsoft Learn

Views in Relational Databases

Views are virtual tables based on the result-set of a SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL statements and functions to a view and present the information as if the data were coming from a single table.

What is a View?

A view is a database object that provides a named query. You can think of a view as a stored query or a "virtual table" that doesn't store data itself but rather retrieves data from one or more underlying tables when it's queried.

• Simplicity: Views can simplify complex queries by abstracting away the underlying table structures and join conditions.
• Security: Views can be used to restrict access to sensitive data by exposing only specific columns or rows from a table.
• Consistency: Views can enforce consistent data presentation and business logic across different applications.
• Data Abstraction: Views allow you to present data in a different structure without altering the underlying database schema, facilitating database evolution.

Creating a View

The basic syntax for creating a view is:

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

Example: Creating a Simple View

Consider a table named Customers with columns CustomerID, FirstName, LastName, and City.

CREATE VIEW LondonCustomers AS
    SELECT CustomerID, FirstName, LastName
    FROM Customers
    WHERE City = 'London';

Once created, you can query this view like a regular table:

SELECT * FROM LondonCustomers;

Types of Views

Most database systems support several types of views, including:

• Simple Views: Based on a single table.
• Complex Views: Based on multiple tables joined together.
• Updatable Views: Views that can be used to insert, update, or delete rows in the underlying tables. There are restrictions on which views are updatable.
• Indexed Views (Materialized Views): Views where the result set is physically stored and indexed, improving query performance but requiring more storage.

Key Concepts and Considerations

• Performance: While views simplify queries, complex views can sometimes impact performance. Indexed views are often used to mitigate this.
• Updatability: Not all views are updatable. Generally, a view is updatable if it references only one table and includes all the NOT NULL columns from that table. Joins, aggregations, and DISTINCT clauses often make a view non-updatable.
• Dependencies: Views depend on the underlying tables. If an underlying table is altered or dropped, the view may become invalid.

CREATE VIEW CustomerOrderSummary AS
    SELECT
        c.CustomerID,
        c.FirstName,
        c.LastName,
        COUNT(o.OrderID) AS NumberOfOrders,
        SUM(o.TotalAmount) AS TotalSpent
    FROM Customers c
    LEFT JOIN Orders o
        ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID, c.FirstName, c.LastName;

ALTER VIEW view_name AS
    new_select_statement;

ALTER VIEW LondonCustomers AS
    SELECT CustomerID, FirstName, LastName, Email
    FROM Customers
    WHERE City = 'London';

DROP VIEW view_name;

DROP VIEW LondonCustomers;