10 — Views: Saved Queries You Can Query Like Tables

"Just a saved query, why bother" was my view dismissal, and it missed the interface idea entirely. Writing it down made the concept click: *a view is a named, queryable virtual table whose definition is stored but whose data is computed on demand — so it hides query complexity, presents a stable interface, and can restrict which columns are visible, all without duplicating data.* [1]

The framing that landed is the abstraction angle. A raw query joining five tables is a leaky, fragile thing — every consumer has to know the join logic, and a schema change breaks all of them. A view wraps that query under one name, and consumers SELECT from it as if it were a table. The complexity lives in one place; the interface is stable; the data is never duplicated because the view runs its underlying query at access time.

Creating a view

CREATE VIEW stores a named query [2][3]:

CREATE VIEW active_customers AS
SELECT c.name, SUM(o.total) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.active = TRUE
GROUP BY c.id, c.name;

After that, SELECT * FROM active_customers returns the result of that query as if it were a table. The five-line join-and-aggregate logic now has a name, and any consumer — a report, a dashboard, another query — uses the name instead of re-deriving the logic.

Modifying a view

ALTER VIEW changes the definition of an existing view without dropping and recreating it, which matters when other objects depend on it [4][5]:

ALTER VIEW active_customers AS
SELECT c.name, c.email, SUM(o.total) AS total
FROM ...

I reach for ALTER VIEW when the underlying logic needs to evolve but the view's name and contract to consumers shouldn't change.

Dropping a view

DROP VIEW removes the view definition. It does not touch the underlying tables — only the saved query goes away [6][7]:

DROP VIEW active_customers;

The caution here is dependencies: other views, stored procedures, or application queries that referenced this view will break. Dropping a view is safe for the data, but not necessarily safe for its consumers.

What views buy me

Three concrete payoffs, each a different concern:

• Complexity hiding. A reporting query with five joins and three aggregations becomes SELECT * FROM monthly_revenue. The complexity is written once, in the view, and the consumer never sees it.
• A stable interface. If I rename an underlying column, I update the view; consumers keep querying the view's unchanging column names. The view is a contract that shields callers from schema churn.
• Column-level security. A view can expose only some columns of an underlying table. Granting a user access to active_customers instead of customers means they never see the columns I left out (a password_hash, say) [1].

The cost: views compute on access

A standard view stores its definition, not its data — every query against it re-runs the underlying SELECT. That's fine when the underlying query is cheap, but a view wrapping an expensive multi-table aggregation will be expensive every time someone queries it. When a view is hit heavily and its data changes slowly, the next step up is a materialized view (where supported), which persists the result and refreshes on demand. The roadmap focuses on plain views; I flag materialized views only as the answer to "this view is too slow to recompute on every read."

How I use this

The habit I keep is to reach for a view whenever the same non-trivial query is about to be consumed more than once. The first time I write a join, it lives in a query; the second place that needs the same logic, I lift it into a view and point both consumers at it. That stops the "five copies of the same join drifting apart" problem and gives me one place to fix, optimize, or secure the logic. And for any column I don't want a class of users to see, I expose a view that omits it and grant access to the view, not the table.

References

[1] DataCamp, "Views in SQL," datacamp.com, 2024. [Online]. Available: https://www.datacamp.com/tutorial/views-in-sql

[2] SQLShack, "How to create a view in SQL Server," sqlshack.com, 2024. [Online]. Available: https://www.sqlshack.com/how-to-create-a-view-in-sql-server/

[3] YouTube, "SQL Views in 4 minutes," 2023. [Online]. Available: https://www.youtube.com/watch?v=vLLkNI-vkV8

[4] SQLShack, "Create View — Modifying Views in SQL Server," sqlshack.com, 2024. [Online]. Available: https://www.sqlshack.com/create-view-sql-modifying-views-in-sql-server/

[5] YouTube, "SQL Views Tutorial," 2023. [Online]. Available: https://www.youtube.com/watch?v=cLSxasHg9WY

[6] TutorialsPoint, "DROP or DELETE a View," tutorialspoint.com, 2024. [Online]. Available: https://www.tutorialspoint.com/sql/sql-drop-view.htm

[7] Study.com, "SQL DROP VIEW Tutorial," study.com, 2024. [Online]. Available: https://study.com/academy/lesson/sql-drop-view-tutorial-overview.html