# Database Views

A **Database View** is a virtual table based on the result-set of an SQL statement. It 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.

## 1. The Core Concept

A view does not store data itself (except for Materialized Views). It stores a **query**. When you query a view, the database engine runs the underlying query and presents the results as if they were a table.

*   **Analogy:** A "Saved Search" or a "Shortcut".
*   **Virtual:** It takes up very little space (just the definition).

## 2. Creating a View (SQL)

```sql
CREATE VIEW ExpensiveProducts AS
SELECT ProductName, Price
FROM Products
WHERE Price > 100;
```

Now you can query it like a table:
```sql
SELECT * FROM ExpensiveProducts;
```

## 3. Materialized Views

A standard view is calculated every time you access it. A **Materialized View** actually stores the result of the query on disk.

*   **Pros:** Extremely fast reads (no need to join tables or calculate aggregates on the fly).
*   **Cons:** Data can become stale. You must refresh the view (manually or automatically) when the underlying tables change.
*   **Use Case:** Complex reports, dashboards, data warehousing.

## 4. Benefits

1.  **Simplification:** Hides complex joins and logic from the user. Instead of writing a 50-line query, the user just does `SELECT * FROM ReportView`.
2.  **Security:** You can restrict access to specific rows or columns. Give a user access to a View that excludes the `Salary` column, rather than giving access to the full `Employees` table.
3.  **Abstraction:** If you change the underlying table structure (normalization), you can update the View definition so that the application code doesn't break.

## 5. Drawbacks

*   **Performance:** Standard views don't improve performance (they might hide inefficient queries).
*   **Update Restrictions:** You often cannot `INSERT` or `UPDATE` data through a view, especially if it involves joins or aggregates.

[[programming/database-basics]]
[[programming/database-normalization]]
[[programming/cqrs]]