8. Views

Database Views

A database view is a named query stored within the database, acting like a virtual table. When you create a view, you effectively save a frequently used query with a specific name so you can reuse it without manually re-entering the query each time.

Why Use Views?

• Simplify Repetitive Queries: Instead of re-typing a query (like a complex JOIN) every time, you can create a view.

• Virtual Table: A view does not store data physically (in most cases); it references the underlying tables.

• Security and Access Control: You can grant users access to specific columns in a view while hiding others.

• Logical Data Independence: If table structures change, you can adapt the view accordingly, allowing client applications to remain unaffected.

Updating Data Through Views

You can typically perform UPDATE or DELETE operations through a view if:

• The view is based on a single table (or certain joins without UNION, DISTINCT, HAVING, etc.).

• The view does not contain set operations (such as UNION, EXCEPT, INTERSECT) or aggregations that prevent direct data modification.

If you include operations like CROSS JOIN, UNION, UNION ALL, EXCEPT, DISTINCT, HAVING, or GROUP BY, the view generally becomes read-only.

WITH SCHEMABINDING

• Using WITH SCHEMABINDING in a view definition prevents underlying table columns from being modified in ways that break the view.

• Once a view is created with WITH SCHEMABINDING, any attempt to change the schema (like renaming columns) of the referenced tables will fail unless you remove or alter the view first.

Example Scenario

Suppose you frequently run this query to join data from customers and payments:

SELECT
    customerName,
    checkNumber,
    paymentDate,
    amount
FROM
    customers
INNER JOIN
    payments USING (customerNumber)

Instead of saving this query in a file, you can create a view:

CREATE VIEW CustomerPaymentsView AS
SELECT
    customerName,
    checkNumber,
    paymentDate,
    amount
FROM
    customers
INNER JOIN
    payments USING (customerNumber);

Now, retrieving the same information is as simple as:

SELECT *
FROM CustomerPaymentsView;

This approach is more convenient, especially if different users or applications need the same combined data.

Views are Virtual Tables

• A view does not typically hold its own data. It references the original tables’ data.

• When columns in the underlying tables change, it can cause errors if you are not using WITH SCHEMABINDING or if your view references now-nonexistent columns.

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version