--- title: "10 — Views: Saved Queries You Can Query Like Tables" uid: views tags: ["abstraction", "sql", "roadmap:sql", "security", "drop-view", "create-view", "alter-view", "views"] excerpt: "A view is a named, queryable virtual table whose definition is stored but whose data is computed on demand — hiding complexity, enforcing a stable interface, restricting columns." date: 2026-08-13T03:27:36+0000 source: https://www.aveshina.my.id/en/blog/views --- "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. ```figure ``` ## 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](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/](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](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/](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](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](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](https://study.com/academy/lesson/sql-drop-view-tutorial-overview.html) ```quiz Q: What does a standard view actually store? - The query definition only — data is computed on access - A full copy of the underlying rows, kept in sync automatically correct: 0 explain: A plain view stores its SELECT definition. Each time it's queried, the underlying query runs against the base tables. (Materialized views are the exception that persist results.) Q: Which benefit best describes a view that exposes only name and email from a customers table? - Column-level security — consumers can't see omitted columns like password_hash - Faster writes, because the view caches INSERTs correct: 0 explain: By exposing only chosen columns and granting access to the view rather than the table, a view acts as a security boundary over sensitive columns. Q: What does DROP VIEW affect? - Only the view definition; underlying tables and their data are untouched - Both the view and the underlying tables correct: 0 explain: DROP VIEW removes the saved query. The base tables and their rows are untouched, though any consumers that referenced the view will now error. Q: When should you ALTER VIEW rather than drop and recreate it? - When other objects depend on the view and you want to preserve its name/contract while changing the logic - Never; dropping and recreating is always equivalent correct: 0 explain: ALTER VIEW changes the definition in place, preserving the view's name and dependencies. Dropping would break dependent objects until the view is recreated. Q: A view wrapping an expensive aggregation is queried heavily and re-runs every time. The usual next step is… - a materialized view, which persists the result and refreshes on demand - dropping all indexes from the underlying tables correct: 0 explain: Because a plain view recomputes on every access, a slow heavily-hit view is the canonical case for a materialized view that stores its result and refreshes explicitly. ```