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)
CREATE VIEW ExpensiveProducts AS
SELECT ProductName, Price
FROM Products
WHERE Price > 100;Now you can query it like a table:
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
- Simplification: Hides complex joins and logic from the user. Instead of writing a 50-line query, the user just does
SELECT * FROM ReportView. - Security: You can restrict access to specific rows or columns. Give a user access to a View that excludes the
Salarycolumn, rather than giving access to the fullEmployeestable. - 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
INSERTorUPDATEdata through a view, especially if it involves joins or aggregates.
programming/database-basics programming/database-normalization programming/cqrs